SQL Joins

Question 1

What are join clauses?

Answer 1

Simply put, JOIN clauses help link tables within a database together. They help transform a data store from a series of tables — which track information, but aren’t particularly useful for analysis — into an interconnected network of data points that can be aggregated and summarized in many different ways.

Question 2

What is an example of a join clause situation?

Answer 2

Here’s an example: let’s say we have two tables in a database that tracks sales from an e-commerce company. One table is called customers, and contains data individual customers, like first_name and email. The second table is called orders, and contains information on individual orders that have been placed — like order_date and product.

Each order in our database is placed by a customer, but we don’t keep the customer’s information within the orders table. Why not? Because if the same customer placed multiple orders, and we kept track of customer information within the orders table, we’d be duplicating data unnecessarily. By separating customer information into its own customers table, we can reduce redundancy and make updates and changes much easier to handle.

So, we include a field called customer_id within each record on the orders table. this ID is linked to a customer_id on the customers table, which contains non-redundant data for each individual customer.

When we want to bring two tables together, we use a JOIN statement to combine data as necessary.

Question 3

What is an inner join?

Answer 3

INNER JOIN returns a list of rows for which there is a match in both tables specified. It’s the default join type, so if you just type JOIN without specifying any other conditions, an INNER JOIN will be used.

Question 4

What is a left join?

Answer 4

LEFT JOIN will return all results from the left table in your statement, matched against rows in the right table when possible. If a row in the left table does not contain a corresponding match in the right table, it will still be listed — with NULL values in columns for the right table.

Question 5

What is a right join?

Answer 5

RIGHT JOIN will return all results from the right table in your statement, matched against rows in the left table when possible. If a row in the right table does not contain a corresponding match in the left table, it will still be listed — with NULL values in columns for the left table.

Question 6

What is a full join?

Answer 6

FULL JOIN will return all results from both the left and the right tables in your statement. If there are instances in which rows from the left table do not match the right table or vice versa, all data will still be pulled in — but SQL will output NULL values in all columns that are not matched.

Question 7

What is the difference between an inner join and a left join?

Answer 7

When constructing a SELECT query that combines two or more tables, choosing the right JOIN type is half the battle. So how do we know when to use INNER JOIN, and when to use the more complex variants like RIGHT JOIN and LEFT JOIN?

Simply put, INNER JOIN should be used when we want to exclude all records that do not match both of the tables we’re joining.

Question 8

What is the difference between LEFT JOIN and RIGHT JOIN?

Answer 8

LEFT JOIN and RIGHT JOIN actually both do very similar things: they display the results of a JOIN query including all records on a given table. The only difference is that LEFT JOIN displays all records on the left table of the query, and RIGHT JOIN displays all records on the right table!

SELECT s.first_name AS student_name, a.first_name AS advisor_name
FROM students AS s
LEFT JOIN advisors AS a ON s.advisor_id = a.advisor_id;

\+--------------+--------------+
| student_name | advisor_name |
\+--------------+--------------+
| Alvin        | James        |
| Tanisha      | Amy          |
| Felix        | Amy          |
| Tracy        | Lamar        |
| Jess         | NULL         |
\+--------------+--------------+
5 rows in set (0.01 sec)
*/

Notice that the advisor ‘Anita’ is excluded in the table above, because she is not assigned to any students.

On the other hand, a RIGHT JOIN of students onto advisors will show a list of all students who are assigned to advisors, plus a list of advisors not assigned to students — because advisors is the RIGHT table:

SELECT s.first_name AS student_name, a.first_name AS advisor_name
FROM students AS s
RIGHT JOIN advisors AS a ON s.advisor_id = a.advisor_id;

/*
\+--------------+--------------+
| student_name | advisor_name |
\+--------------+--------------+
| Alvin        | James        |
| Tanisha      | Amy          |
| Felix        | Amy          |
| Tracy        | Lamar        |
| NULL         | Anita        |
\+--------------+--------------+
5 rows in set (0.00 sec)
*/