4 Scenarios When To Use a SQL Join

What is a SQL Join?

A SQL Join combines records or rows from multiple tables depending on common columns(s). To merge data from various tables, you'll need to use SQL Joins. But if you are not sure when to use SQL Joins.

It's all OK. This article will show four scenarios when you might need to use SQL Joins and show you how to use them.

You can use different types of SQL joins depending on the outcome you want. There are 4 types of SQL Joins: INNER, LEFT, RIGHT and FULL. This article will show the differences between them by walking you through 4 scenarios.

These four types of SQL Joins have a distinct effect on how data from separate tables are combined using the relevant columns.

The Inner Join finds all records that have identical values in both tables.

The Left Join finds all records from the Left table and solely those with identical values from the Right table.

The Right Join finds all records from the Right table and solely those with identical values from the Left table.

Finally, a Full Join finds all records from both tables, regardless of whether or not the values match.

You can refer to this Introduction to SQL Joins guide to read more about each of these types of joins.

Don't worry if this sounds complicated. We will be walking through all these with our example scenarios below.

Scenario 1
Determine Number of Sales Made by Your Branch using an Inner Join

Let's look into a Pizza Restaurant and its food delivery to learn more about Inner Joins.

We will see how many total orders of each different food item each branch of the Pizza Restaurant delivered.

Table 'PizzaCompany' will have all the various branches within several cities. Table 'Foods' will keep track of all the food delivery details for these branches.

Table Pizza Company

CompanyID	CompanyName	CompanyCity
101	Pizza Corner	Zurich
102	Dominos	Mumbai
103	Corner Food	Pune

Table Foods

FoodsID	FoodsName	FoodsSold	CompanyID
1	Garlic Pizza	5	102
2	Corn Pizza	6	102
3	Cheese Pizza	3	101
4	French Pizza	7	101

These tables can be joined using an Inner Join statement on the field CompanyID to show each pizza shop's Pizza products and units.

SELECT pz.CompanyCity, pz.CompanyName, pz.CompanyID AS PizzaCompanyID, f.CompanyID AS FoodsCompanyID, f.FoodsName, f.FoodsSold
FROM PizzaCompany pz
INNER JOIN Foods f
ON pz.CompanyID = f.CompanyID

The query's results are displayed below. As you can see, an Inner Join produces the rows where the CompanyID was an exact match in both the Foods table and PizzaCompany table.

It also provides all other columns from both tables for every matched row.

CompanyName	CompanyCity	PizzaCompanyID	FoodsCompanyID	FoodsName	FoodsSold
Dominos	Mumbai	102	102	Garlic Pizza	5
Dominos	Mumbai	102	102	Corn Pizza	6
Pizza Corner	Zurich	101	101	Cheese Pizza	3
Pizza Corner	Zurich	101	101	French Pizza	7

You'll see that the query results didn't include CompanyID = 103 because it doesn't match anything in the Foods database.

So to summarize, an Inner Join will select all rows & columns from both tables, where the values in the chosen columns are an identical match.

The values must be completely identical. For example, if we were joining on Names and the value in Table 1 was "John Snow," and the value in Table 2 was "john snow," there would be no match. These values are formatted in different cases.

Finally, the general SQL syntax for Inner Join is

SELECT Column_list
FROM TABLE1
INNER JOIN TABLE2
ON Table1.ColName = Table2.ColName

That's it for Inner Join. But in our example above, what if we also wanted to include the restaurant Corner Food. It had made no deliveries and did not have any orders on the Foods table.

Well, then that is a job for Left Join.

If we use the same query and instead of Inner Join, we use Left Join, we will get the following result.


SELECT pz.CompanyCity, pz.CompanyName, pz.CompanyID AS PizzaCompanyID, f.CompanyID AS FoodsCompanyID, f.FoodsName, f.FoodsSold
FROM PizzaCompany pz
LEFT JOIN Foods f
ON pz.CompanyID = f.CompanyID

The Result table for Left Join

CompanyName	CompanyCity	PizzaCompanyID	FoodsCompanyID	FoodsName	FoodsSold
Dominos	Mumbai	102	102	Garlic Pizza	5
Dominos	Mumbai	102	102	Corn Pizza	6
Pizza Corner	Zurich	101	101	Cheese Pizza	3
Pizza Corner	Zurich	101	101	French Pizza	7
Corner Food	Pune	103	NULL	NULL	NULL

Here we can see that we have one more row than the Inner Join result. It includes the CompanyID 103 and shows the "NULL” value on the right side as there is no match data from the Table Foods.

All values of Table PizzaCompany are returned with the corresponding records from Table Foods. Since ALL values of the Left table PizzaCompany are returned, even if they don’t match the Right table, it's called a Left Join.

Don't worry if you do not completely understand. Let us look at another scenario.

Scenario 2
Counting number of movies for an Actor through Left Join

In this scenario, we will be counting the number of movies an Actor has appeared in.

Table Actors

Name	City	Age
Shahrukh Khan	Mumbai	55
Tom Cruise	NYC	59
Tom Hanks	Los Angeles	65
Jackie Chan	Hong Kong	67

Table Movies

MoviesNo	Name
10	Shahrukh Khan
20	Tom Cruise
30	Tom Hanks
20	Gael Garcia Bernal

These tables can be joined using a Left Join statement on the field Name to show the Actors’ details and the number of movies they have appeared in.

SELECT Actors.NAME,Movies.MovieNo
FROM Actors LEFT JOIN Movies
ON Movies.Name = Actors.Name;

The query’s outputs are displayed below. As you can see, a Left Join produces the rows where the Name was an exact match in both the Actors table and Movies table.

The result would include the Actors table data even if there was no corresponding record in the Movies table. It provides "Null" values for data that did not pair.

Result Table

Name	MoviesNo
Shahrukh Khan	10
Tom Cruise	20
Tom Hanks	30
Jackie Chan	Null

So to explain Left Join, let's say we wish to connect two tables, 1 and 2. The SQL Left Join provides all of the values/rows in the left table and the rows in the right table that match those in Table 1.

Here is a general form of query for SQL Left Join,

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Occasionally, we'd like to include records in our analysis that are not relevant. For instance, you may only want to note that certain companies don't appear in other tables, as we saw in earlier Inner Join scenarios.

It could be a part of some sort of check, or it could be as simple as counting the number of times something happens.

Once again, you can refer to to this to understand the Left Join more in-depth.

But what about if the order of tables was flipped. If the Actors table was on the left side and the number of Movies table was on the right side.

Then we would use something called a Right Join. Let’s take a look at an example of that below.

Scenario 3
Movie theater seat arrangement with ticket numbers through Right Join

The Right Join does the same as the Left Join but on the opposite side of the table.

In this scenario, we will look at two datasets. Table Customer contains information about people watching movies and their profile information. Table Ticket contains information on the movie's ticket numbers and seat types with time slots.

Using these tables and the Right Join, we will be discovering which seats are occupied and which ones are not.

Following is our Customer details table,

Table Customer

CustomerNo	CustomerName	ticket_no
B1	Sandy	AE11
B2	Mandy	AE12
B3	Candy	AE13

Table Ticket

ticket_no	SeatType	TimeSlot
AE11	Upper Chairs	9-11
AE12	Upper Balcony	11-13
AE13	Lower Balcony	13-15
BE11	Lower Chair	15-17
AE15	VIP	17-19

For joining these, the following is a SQL query that will use to make a Right Join among these two tables on the ticket_no column,

SELECT CustomerNo , CustomerName , SeatType, TimeSlot
from Customer
RIGHT JOIN ticket on customer.ticket_no = ticket.ticket_no;

CustomerNo	CustomerName	ticket_no	SeatType	TimeSlot
B1	Sandy	AE11	Upper Chair	9-11
B2	Mandy	AE12	Upper Balcony	11-13
B3	Candy	AE13	Lower Balcony	13-15
NULL	NULL	BE11	Lower Chair	15-17
NULL	NULL	AE15	VIP	17-19

By following the Right Join in this example, we can observe that the ticket numbers with no customer details have “NULL” results. This shows us which ticket numbers have been purchased and which ones have not.

Let’s summarize the Right Join. Like a Left Join, a Right Join is similar. But unlike a Left Join, a Right Join returns all rows that match both tables and ALL rows from the Right table.

Following is the general syntax of RIGHT join,

SELECT table1.column1,table1.column2,table2.column1,
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

So far, we have looked at joins where only matched values return OR matched values AND rows from 1 of the tables (Left or Right).

But what if we need a result where we want matched values AND rows from both tables even if they are not matched.

That’s a job for Full Join.

Scenario 4
How to keep records of students in Sports clubs by Full Join

A SQL Full Join will return all of the rows from both tables, whether the value of the joining column matches or not. A NULL value will display in a field if a row does not match in either of the tables.

Let us understand with a simple example,

We have a unique attribute (StudentID) in two tables: Student and Sports, which link them together.

Table Student

StudentID	StudentName	City	Age
301	Jacky Chan	Tokyo	18
302	Tom Cruise	Berlin	17
303	Dale Steyn	Africa	19
304	Tom Curren	Helsinki	18
305	Michael Clarke	Mosko	20
306	Steve Smith	Nairobi	17

Table Sports

StudentID	Sport_ID	SportName
301	JH-1	Karate
302	TC-2	Football
303	DS-5	Cricket
306	SS-7	Baseball

When we perform the Full Join between the Sports table and the Student table, we can see the result below,

The following rows appear in the output of a SQL Full Outer Join.

● It shows rows from both the Left and the Right tables.
● When the values in the StudentID column do not match, the table shows NULL values.

StudentID	StudentName	City	Age	StudentID	Sport_ID	SportName
301	Jacky Chan	Tokyo	18	301	JH-1	Karate
302	Tom Cruz	Berlin	17	302	TC-2	Football
303	Dale Steyn	Africa	19	303	DS-5	Cricket
304	Tom Curren	Helsinki	18	NULL	NULL	NULL
305	Michael Clarke	Mosko	20	NULL	NULL	NULL
306	Steve Smith	Nairobi	17	306	SS-7	Baseball

Following is the general form of Full Outer Join query,

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name

Summary of Scenarios

● An Inner Join is used when ONLY exact matches from both tables are needed
● A Left Join obtains all rows from the left table AND the exact matches
● A Right Join is similar to a Left Join, except it returns all rows from the right table AND the exact matches
● A Full Join returns every row in both tables including the exact matches

Further Reading to Understand SQL Joins

1.Intro to SQL Joins
2.Reasons to do a SQL Join
3.SQL Join Examples

Go Up