I would would cover everything you need to know about SQL Joins in this lesson. And you will see it’s quite clear.
A JOIN in SQL is used to combine record from two or more tables. The combinations is based on a given criteria. Also note that the two tables need be related in some way.
We would be working with the following tables.
Customers Table: Contains list of customers
[table id=3 /]
Orders Table: Contains orders placed by the customers
[table id=4 /]
We would cover :
1. INNER JOIN
You use INNER JOIN to return records that have matching values in both tables. Note that this is the same as JOIN. Therefore, if you use only JOIN, the it assumes INNER JOIN.
Now copy and execute the code below in SQL Server. You will have the result in Table 1.0.
Listing 1.0: INNER JOIN (JOIN)
[table id=5 /]
Table 1.0: INNER JOIN (JOIN)
You should note in Table 1.0, that only the records where there is matching CustomerID is returned. I would recommend you try this yourself. You can get the excel table from here. Then see how to import excel to MSSQL from here.
2. LEFT JOIN
Use LEFT JOIN to return all records in the left table plus matching records in the right table. Null is used in place of missing values in the right table. Note that LEFT JOIN is the same as LEFT OUTER JOIN.
I have written the query. You can copy and paste the code below in SQL Server. Then run it. You will have the result shown in Table 1.1
Listin 1.1: SQL Query for LEFT JOIN (LEFT OUTER JOIN)
[table id=6 /]
Table 1.1: Table for LEFT JOIN(LEFT OUTER)
We see that in Table 1.1, all the records for Customers Table (Left Table) is included. Furthermore, for customers with no orders, NULL is used.
3. RIGHT JOIN
You use RIGHT JOIN to return all records from the right table table plus matching records in the left table. It is same as RIGHT OUTER JOIN. Null is used in place of missing values in the right table. I have written the query for you. If we run the code below, then we would have the result in Table 1.3. You can view RIGHT JOIN as opposite of LEFT JOIN. I recommend you try it yourself.
Listing 1.3: SQL Query for RIGHT JOIN (RIGHT OUTER JOIN)
You can see in Table 1.3 that all the records for the Orders table (right table) is included.
[table id=7 /]
Table 1.3: Result of RIGHT JOIN (RIGHT OUTER JOIN)
4. FULL JOIN
We use FULL JOIN to return all records from both tables. You can view this as a combination of LEFT JOIN and RIGHT JOIN. I have written the query as shown below. I also recommend you try it yourself.
Listing 1.4: SQL Query FULL JOIN (FULL OUTER JOIN)
[table id=8 /]
Table 1.4: FULL JOIN
5. CROSS JOIN
You use CROSS JOIN to return a result that is number of rows of first table multiplied by number of rows in second table. We also call this Cartesian Product. For our example, the output would be a 25 rows table!
I would only provide the query for you. So you run it yourself and see the result. You have the SQL query in Listing 1.5. So run it and leave a comment to let me know what you have.
Listing 1.5: CROSS JOIN (Cartesian Product)
Not that the SQL query for CROSS JOIN does not include the ON keyword
6. Self Join
We use to indicate a join of a table with itself. I would provide you with an example of this as shown in Listing 1.6.
Listing 1.6: Self Join
The result of this code is given in Table 1.5
[table id=9 /]
Table 1.5: Self Join
Also note that we don’t write a ‘SELF JOIN’ query. We simple join a table with itself using any of the joins we have discussed.