JOINS - FULL, CROSS JOIN

Understand how FULL JOIN and CROSS JOIN work

10 minutesVideo LessonPDF notes

🎯 Free Guest Mode: You are learning for free. Sign in to save your completion progress and quiz answers.

Ready to continue?

Mark this lesson as complete when you're ready to proceed.

Key moments

Joins and FROM Clause — Joins are SQL instructions placed in the FROM clause used to combine data sets.
Primary and Foreign Keys — A primary key uniquely identifies a row, while a foreign key links to another table's primary key.
Joining Logic and Risks — Tables can be joined on any columns of the same type, but joining without foreign keys is risky.
INNER JOIN (Default) — INNER JOIN combines rows only when there are matching values in the specified common column.
LEFT JOIN Behavior — LEFT JOIN returns all rows from the first table, using NULLs for non-matching values from the right table.
RIGHT JOIN Behavior — RIGHT JOIN returns all rows from the right table, using NULLs for non-matching values from the left table.
FULL JOIN (OUTER JOIN) — FULL JOIN combines all data from both tables, using NULLs where matches do not exist.
UNION and UNION ALL — UNION stacks data vertically and deduplicates, while UNION ALL stacks data vertically and keeps duplicates.
CROSS JOIN (Cartesian) — CROSS JOIN combines every row of the first table with every row of the second table.

PDF notes

Bottom line: You will be able to select the appropriate SQL join type—INNER, LEFT, RIGHT, FULL, or CROSS—to accurately combine data from two or more tables. You will understand how primary and foreign keys facilitate these relationships and how to handle non-matching rows using NULL values.

This lesson on JOINS - FULL, CROSS JOIN is hands-on and example-driven. You will be able to select the appropriate SQL join type—INNER, LEFT, RIGHT, FULL, or CROSS—to accurately combine data from two or more tables. You will understand how primary and foreign keys facilitate these relationships and how to handle non-matching rows using NULL values.

What You'll Be Able To Do

Define the roles of Primary and Foreign Keys in establishing table relationships.
Write queries using INNER JOIN to retrieve only matching records between two datasets.
Apply LEFT JOIN to retain all records from the primary table, filling non-matches with NULLs.
Distinguish between the vertical stacking operations UNION and UNION ALL.
Construct a FULL JOIN query to include all rows from both joined tables.
Explain the result set generated by a CROSS JOIN (Cartesian product).

Topics Covered in JOINS - FULL, CROSS JOIN

Joins and FROM Clause (0:00 - 0:30) — Joins are SQL instructions placed in the FROM clause used to combine data sets.
Primary and Foreign Keys (0:30 - 1:15) — A primary key uniquely identifies a row, while a foreign key links to another table's primary key.
Joining Logic and Risks (1:15 - 2:30) — Tables can be joined on any columns of the same type, but joining without foreign keys is risky.
INNER JOIN (Default) (2:30 - 4:30) — INNER JOIN combines rows only when there are matching values in the specified common column.
LEFT JOIN Behavior (4:30 - 5:30) — LEFT JOIN returns all rows from the first table, using NULLs for non-matching values from the right table.
RIGHT JOIN Behavior (5:30 - 6:45) — RIGHT JOIN returns all rows from the right table, using NULLs for non-matching values from the left table.
FULL JOIN (OUTER JOIN) (6:45 - 7:45) — FULL JOIN combines all data from both tables, using NULLs where matches do not exist.
UNION and UNION ALL (7:45 - 8:30) — UNION stacks data vertically and deduplicates, while UNION ALL stacks data vertically and keeps duplicates.
CROSS JOIN (Cartesian) (8:30 - 9:00) — CROSS JOIN combines every row of the first table with every row of the second table.

SQL Cheat Sheet

JOIN or INNER JOIN — Includes only rows with matching values in both tables

SELECT * FROM customer JOIN event ON customer.id = event.customer_id;

LEFT JOIN — Includes all rows from the left table, matching rows from the right

SELECT * FROM customer LEFT JOIN event ON customer.id = event.customer_id;

RIGHT JOIN — Includes all rows from the right table, matching rows from the left

SELECT * FROM event RIGHT JOIN action_types ON event.action_id = action_types.id;

FULL JOIN or OUTER JOIN — Includes all rows from both tables, filling non-matches with NULL
```
SELECT * FROM teacher FULL JOIN student ON teacher.age = student.age;
```
UNION — Stacks two result sets vertically, removing duplicate rows
```
SELECT age FROM teacher UNION SELECT age FROM student;
```
UNION ALL — Stacks two result sets vertically, retaining all duplicate rows
```
SELECT age FROM teacher UNION ALL SELECT age FROM student;
```
CROSS JOIN — Combines every row from the first table with every row of the second
```
SELECT * FROM teacher CROSS JOIN student;
```

Comparison Table

Join Type	Matching Requirement	Non-Matching Rows
INNER JOIN	Match required in both	Excluded
LEFT JOIN	Match required in right	Left rows kept, right NULL
FULL JOIN	No match required	All rows kept, NULLs used

Common Pitfalls

Mistake: Joining tables using columns that are not unique identifiers. Avoid: Always use columns that are unique or foreign keys to prevent duplicate data.
Mistake: Assuming JOIN means FULL JOIN and expecting all data. Avoid: Use INNER JOIN (or JOIN) only when you want records that exist in both tables.
Mistake: Joining tables on columns of the same type but unrelated meaning. Avoid: Ensure the columns share a common dimension or logical relationship (e.g., IDs).

FAQs

Why is RIGHT JOIN rarely used in practice? Any query written using a RIGHT JOIN can be rewritten as a LEFT JOIN, which is the more conventional and widely used standard.
What happens if I use JOIN without specifying INNER, LEFT, or RIGHT? JOIN is the default syntax and is equivalent to an INNER JOIN, only returning rows with matches in both tables.
Do the columns used for joining have to be Primary or Foreign Keys? No, they only need to be of the same data type, but using PK/FK ensures logical and accurate relationships.
How does UNION differ from UNION ALL? UNION automatically removes duplicate rows from the combined result set, while UNION ALL stacks data vertically and retains all rows, including duplicates.

Common pitfalls

Mistake: Joining tables using columns that are not unique identifiers. Avoid: Always use columns that are unique or foreign keys to prevent duplicate data.
Mistake: Assuming JOIN means FULL JOIN and expecting all data. Avoid: Use INNER JOIN (or JOIN) only when you want records that exist in both tables.
Mistake: Joining tables on columns of the same type but unrelated meaning. Avoid: Ensure the columns share a common dimension or logical relationship (e.g., IDs).

If you need to merge two or more data sets in SQL, you can use joins. A join is SQL instruction that you put in the from clause of your query. It is used to identify the tables you're querying and how they should be combined. There are multiple ways you can combine data sets. So, therefore, there are multiple SQL joins you need to learn. Optionally, if you want to practice, I'll explain how you can pull my public Postgres Docker image, which already has all the tables and data that we'll use in this tutorial. But, first, let's quickly refresh what primary and foreign keys are. Typically, in relational database, data is organized into different tables made of attributes, columns, and records, rows. Now, the primary key in SQL is a single column or sometimes multiple columns that can uniquely identify a row in a table. This is usually the ID column, which is short for identifier. Now, another column in a table that establishes relationship with another table's primary key via shared value is called a foreign key. Foreign keys are also typically titled IDs but prepended with the name of the reference table. Now, let's go over a real-world example. Let's say you have a customer table. It could contain data from CRM tool like Salesforce, which includes users who are paid customers, and you may have another event table with data from analytics tool like Mixpanel that tracks all the user's actions. Notice that there is a common column, customer ID between the two tables. In the customer table, the customer ID column is the primary key. Now, in the event table, the customer ID column is the foreign key, since that column refers to the customer ID column in the customer table. We can use this relationship to join the two tables together to get the customer and events data in one table. Now, for a join operation, technically you only need two columns of the same type, one from each table. It does not matter if they are primary or foreign keys. For instance, you can join tables using age column from both the teacher and student tables. However, you need to be very careful when joining tables without foreign keys. For example, you could technically join a car table using the model column and the item table using the color column. They have the same type, so it's possible. But, in reality, it wouldn't make any sense. All right, let's go back to the customer and event tables. Each type of a joint is used to answer a specific business question. For example, what if you want to get only active users? In other words, we want to get only users that have performed an action. You would use inner joint to join the tables together. An inner join combines the columns on a common dimension when possible, and only includes data for the columns that share the same values in a common column. In the example, the customer ID would be the common dimension used for the inner joint. This is the default type of joint in SQL. In fact, you don't even need to specify inner joint when writing a query. Only writing join is the inner join. SQL first creates a new table with the columns from both tables you're trying to combine. This mainly happens because we used asterisk to select all columns. However, you can also select only specific columns for the join. It then tries to find values that match between the columns you specify in the on statement. Putting the table name with a period before the column name makes it clear which two columns of the tables SQL will be looking for matches between. SQL then starts with the first value of the specified column in the first table, customer customer ID, and then looks through every value in the specified column of the second table, event customer ID for a match. If there is a match, it copies the data from both the row of the first table and the row of the second table and puts it in the newly created table. SQL will not add any rows that did not have a match. Be sure to know what data you want in the final table, so that the data left out does not affect your analysis. Another thing to consider is that SQL will join the rows every time there is a match. So, if your data in the columns you're joining on are not unique, you'll get duplicate data in the final table. You can use my Docker image and run the same join. You'll get the same result. Now, what if you want to get all users from the customer table and only the actions that these users have done? This is the second most common type of join in SQL, where left refers to the first table. Initially, SQL will attempt to match all the users from the left table with actions in the right table. This is again a conceptual model. In practice, the database performs a more optimized comparison that is not visible to the end-user. However, there is a significant difference in how SQL treats the left table. For any rows in the first table that do not have a match, SQL will still add this rows to the new table, placing nulls as the values. Before moving to the next join, let's slightly modify the source tables. We'll still use the same customer table, but now we'll create a couple more. First of all, we'll dedicate a table to define action types. In the future, you can expand this table to create more actions. The new event V2 table will contain the same information about customer activity, but now we'll use customer ID and action ID foreign keys to establish relationship with other two tables. It's a common practice in SQL to spread data across multiple tables. In this case, we'll use right join to combine the tables. Right in this context refers to the right action table. So, we aim to get all the action types and only associated events. Like a regular join, SQL will first try to find all events and match them with the action ID from the right table. Then, since it's a right join, SQL will copy the remaining action types from the right table and use null values for the events. This is one of the rarest types of join in the SQL. The reason for this is that any right join can be rewritten as a left join, which is a more conventional. Now, for the following example, let's use two additional source tables. One table is for the teachers and another one for students. As you can see, we don't have any foreign keys, but since the age column in both tables is of the integer type, we can use it to join the tables. Let's say you want to have a table that combines data from both the teacher and student tables. You would use outer join to merge the table together. Sometimes, it's also referred to as a full join. An outer join combines the columns from all tables based on one or more common dimensions when possible. In this case, that's the age column. It includes all data from all tables. This is the third most common type of join in SQL. This query finds matches and adds them to a newly created table much like a left join. However, after completing the left join of the data, a right join is essentially performed. The following examples will use the same teacher and student tables. The first one is a union. It is the fourth most common type of join in SQL. A union does not attach the data from two tables to a single row. Instead, union stacks two data sets on top of each other into a single table. The data types of columns must be the same as well. Here, we'll select a specific age column. When you perform the union operation, SQL will take all possible values and deduplicate them. On the other hand, the union all operator selects fields from two or more tables similar to union. However, unlike union, union all doesn't ignore duplicate fields, so you'll get 28 twice in this example. And, finally, the fifth most common type of join in SQL is a cross join. A cross join does not look for matches between any values in two data sets. Instead, for each row in the first table, every row of the second table will be attached to it and added to the final table one by one. In the previous video, we discussed how database sharding is performed. So, you may find it interesting as well. Also, if you want to learn more about Kubernetes, such as differences between node port, load balancer, and ingress, you can watch this video. Or, maybe you want to learn more about differences between deployment, stateful set, and daemon set. Thank you for watching and I'll see you in the next video.

Frequently asked questions

Why is RIGHT JOIN rarely used in practice?

Any query written using a RIGHT JOIN can be rewritten as a LEFT JOIN, which is the more conventional and widely used standard.

What happens if I use JOIN without specifying INNER, LEFT, or RIGHT?

JOIN is the default syntax and is equivalent to an INNER JOIN, only returning rows with matches in both tables.

Do the columns used for joining have to be Primary or Foreign Keys?

No, they only need to be of the same data type, but using PK/FK ensures logical and accurate relationships.

How does UNION differ from UNION ALL?

UNION automatically removes duplicate rows from the combined result set, while UNION ALL stacks data vertically and retains all rows, including duplicates.