Learn how to use JOIN to keep both matched and unmatched rows when you join two tables. Show
Joining two or more tables is a skill you need a lot if you’re working with databases. To review and practice your SQL joining skills, I recommend the interactive SQL JOINs course. It contains over 90 exercises and sections on different joining challenges. If you often join tables in SQL, you’ve probably noticed not
all data from one table corresponds to data from another table all the time. Sometimes, you need only the data that have a match in both tables. And sometimes, you also need to keep the unmatched rows. How do you do that in a Which JOIN Gets you the Unmatched Rows?You probably know this already, but it’s worth repeating. The purpose of the The four main
types of
When you use a simple If this is not what you want, the solution is to use the If you’re new to the Get to Know the DataThe data we’ll be working with consists of two tables:
The table
Here’s what the data from the table
There are two columns in the table
And this is the data from the table:
What Happens When you Use the INNER JOIN?To get the employees’ name and the projects they are working on by using the SELECT first_name, last_name, project_name FROM employee e JOIN project p ON e.project_id = p.id; Note: Of course, you can use either Here’s what you get as a result:
The result shows only the rows from the table How do you also get the unmatched rows? Get all the Matched and Unmatched Rows From one TableTo get all of the rows from just one of the tables – the matched rows as well as the unmatched rows – you need to use the Using the LEFT JOINTo get the matched as well as unmatched rows from a table using the SELECT first_name, last_name, project_name FROM employee e LEFT JOIN project p ON e.project_id = p.id; It’s almost the same code as in the previous example. The only difference is it uses the
How do you interpret this data? You’ve got all the employees and the projects they are working on. When there’s a Using the RIGHT JOINNow, let’s write the same code with the SELECT first_name, last_name, project_name FROM employee e RIGHT JOIN project p ON e.project_id = p.id; It’s a mirror image of the previous code. It will return all the data from the right-hand table, This is the query result:
Now, this data shows all the projects that exist in the table Will the FULL JOIN Get you all the Matched and Unmatched Rows?I think the query using the FULL JOIN and its result will speak for itself. Let’s see what it does. First, the query: SELECT first_name, last_name, project_name FROM employee e FULL JOIN project p ON e.project_id = p.id; Again, the only difference from the previous queries is the type
of join used. Everything else is the same. This
Let’s inspect the data a bit. There are rows that are found in both tables. There are, however, rows from the table When to Use Which JOINNow that you have seen what each The The The Here are some more examples for practicing joining the tables. Learn the Different Types of JOIN to Select the one you Need!You see, knowing how these To master and practice this skill, the SQL Basics course and the SQL JOINs course are what you need! If you need advice on how to practice JOINs, here are some tips. What SQL function will return records with matching values in both tables?Note: The FULL OUTER JOIN keyword returns all matching records from both tables whether the other table matches or not.
Which SQL JOIN returns all records from the right table and matched records from the left table?The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1).
What SQL function will return all records from the left table and only the matching records from the right 1 point left join Inner join Right join outer join?The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.
Which type of join combines the results of both left and right outer join?The SQL FULL JOIN combines the results of both left and right outer joins. The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.
|