SQL Foreign Key and Joins

Hello everybody! As I promised I am going to write about Foreign Key and Joins in SQL.

Foreign  Key

Foreign Key are keys that references the primary key of another table. Let's create the table Salesman:

Create Table Salesman

And after I will create the Sale table that will references the Client and Salesman tables:

Create Table Sale

Take a look, I told the database the columns ClientID and SalesmanID are Foreign Key and after I used the keyword REFERENCES to reference the table and between parenthesis the column referenced.

Just remembering, you can not insert an ID for Client and Salesman in Sale table that not exists in Client and Salesman tables. Always a value of a Foreign Key should exists in the referenced table.

Joins

Now let's talk about the existing Joins in SQL:

Inner Join

Inner Join

Starting by INNER JOIN, as the image shows, returns all the data associated. I will select the client's name, salesman's name and the value of the sales using INNER JOIN:

Inner Join

Left Join

Left Join

LEFT JOIN, as the image shows, returns all data from the table on the left side of the JOIN and all the associated data from the right table. 

Left Join

Right Join

Right Join

RIGHT JOIN is like the LEFT JOIN, but inverted. I will change the LEFT JOIN in the previous query for a RIGHT JOIN

Right Join

In that case the client John Doe disappeared.

Outer Join

Outer Join

OUTER JOIN, known as FULL OUTER JOIN, returns all data from all tables.

Outer Join

Left Excluding Join

Left Excluding Join

The contrary of LEFT JOIN, returns all data of the left side table not associated with the right side table.

Left Excluding Join

Right Excluding Join

Right Excluding Join

Like the LEFT EXCLUING JOIN but with the right side.

Right Excluding Join

Outer Excluding Join

Outer Excluding Join

OUTER EXLUDING JOIN returns all data that are not associated.

Outer Excluding Join

You can also mix more than one join:

Mix of Joins

Image explaining all JOINS:

All Join

Alias

As you can see, I used some acronyms in my queries like "SM", "S", "C", "ClientName", "SalesmanName", this is called ALIAS, it is like a nickname for tables and columns. The first three are ALIAS for tables, I used to better identify the tables in my queries; take a look: I used only the alias on my JOINS. And the last two are ALIAS for columns to better identify them in the results. The use of alias is not required, you decide when to use, use if you think that ALIAS will facilitate your queries.