Show
This article looks at different types of SQL joins. If you’re new to the subject you may want to check out the SQL joins article as well. Please note that joins only work with relational databases. Quick review of SQL Join TypesA SQL join tells the database to combine columns from different tables. We normally join tables by matching the foreign keys in one table to the primary keys in another. For example, every record in the
It’s very important that we use The problem with SQL joins explainedEven if we use the
correct fields, there is a trap here for the unwary. It’s easy to check that every record in
But what if things don’t always match? For example, suppose we’re trying to find out which products lack reviews. If we look at the
Every single review refers to a product:
But does every product have reviews? To find out, let’s count the number of products:
We can then combine the
Only 176 of the 200 products have any reviews. As a result, if we count the number of reviews for each product, we’ll only get the counts where there were some reviews—our query won’t tell us anything about products that lack reviews because the inner join won’t find any matching when combining the tables. This query demonstrates the problem:
We’ve ordered the result in ascending order by count; as this shows, the lowest count is 1, when it should be 0. Outer SQL join types to the rescueAll right: we know how many products don’t have reviews, but which ones are they? One way to answer that question is to use the type of SQL join known the left outer join, also called a “left join”. This kind of join always returns at least one record from the first table we mention (i.e., the one on the left). To see how it works, imagine we have two little tables called
while the
If we do an inner join on these two tables, matching
Nothing in the If we do a left outer join, though, the database keeps every record from the left table that lacks a
match. Since there aren’t matching values from the right table, SQL fills in those columns with
Keeping all of the records from the left table turns out to be useful in a lot of different situations. For example, if we want to see which paints don’t have matching fabrics, we can do a left outer SQL join:
This is easier to read if we select only the rows where the values from the right-hand table are
We can use this technique to get a list of products that don’t have any reviews by doing a left outer join and keeping only the rows where
What about right outer SQL join and full outer join?The SQL standard defines two other kinds of SQL join types for the outer join, but they are used
much less often—so much less than some databases don’t even implement them. A right outer join works exactly like a left outer join, except it always keeps rows from the right table and fills columns from the left table with A full outer join keeps all of the information from both tables. If a record on the left lacks a match on the right, the database will fill in the missing right-hand values with
Full outer joins are occasionally useful for finding the overlap between two tables, but in twenty years of writing SQL, I have only ever used them in lessons like this one. Which SQL join type to use?To review, there are four basic types of joins. Inner joins only keep records that match, and the other three types fill in missing values with In general, you’ll only really need to use inner joins and left outer joins. Which join type you use depends on whether you want to include unmatched rows in your results:
For another angle on joins that abstracts away the SQL, check out our article on joins using Metabase’s query builder. Common problems with SQL joinsDoing an inner SQL join instead of an outer joinThis is probably the most common error. Real data often has gaps, and inner joins will discard records without warning you whenever keys don’t line up. Counting the number of rows from one table that don’t have matches in another is a good safety check; if there are any, you should think about using an outer join instead of an inner one. Using SQL joins on “matches” that aren’t meaningfulA person’s weight in kilograms and the value of their last purchase in dollars are both numbers, so it’s possible to do a join by matching them, but the result will (probably) be meaningless. A less frivolous example comes up when one table contains several foreign keys that refer to different tables, which can lead to joining patient data with vehicle registrations instead of appointment dates. Declaring foreign keys in tables can help prevent this. Confusing NULLs in data with NULLs from mis-matchesIf one of the tables in an outer join contains Thanks for your feedback! Get articles like this one in your inbox every month Which join returns all records when there is a match in either left or right table?The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
Which type of join contains all the records from one table and matching records from the other table?A CROSS JOIN , also known as a Cartesian JOIN, returns all rows from one table crossed with every row from the second table. In other words, the join table of a cross join contains every possible combination of rows from the tables that have been joined.
Which type of join can show all the records in the right most table with only the matching records form the left table?FULL JOIN. Full Join or the Full Outer Join returns all those records which either have a match in the left(Table1) or the right(Table2) table.
What type of join returns all rows from the first table and any matching rows from the second table?A cross join, also known as a Cartesian Product join, returns a result table where each row from the first table is combined with each row from the second table.
|