All About SQL Joins (Simple Explanation)

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
2. Left Join
3. Right Join
4. Full Join
5. Cross-Join
6. Self Join

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.

SELECT c.Contact, c.CustomerID, o.CustomerID, o.OrderDetails  
FROM Customers c INNER JOIN Orders o
ON c.CustomerID = o.CustomerID

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

SELECT c.Contact, c.CustomerID, o.CustomerID, o.OrderDetails  
FROM Customers c LEFT JOIN Orders o
ON c.CustomerID = o.CustomerID

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.

SELECT c.Contact, c.CustomerID, o.CustomerID, o.OrderDetails  
FROM Customers c RIGHT JOIN Orders o
ON c.CustomerID = o.CustomerID

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.

SELECT c.Contact, c.CustomerID, o.CustomerID, o.OrderDetails  
FROM Customers c FULL JOIN Orders o
ON c.CustomerID = o.CustomerID

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.

SELECT c.Contact, c.CustomerID, o.CustomerID, o.OrderDetails  
FROM Customers c CROSS JOIN Orders o

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.

SELECT c1.CustomerName, c2.CustomerID 
FROM Customers c1 JOIN Customers c2
ON c1.CustomerID = c2.CustomerID

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.