(click anywhere to close)
OPEN MENU

[SQL] Joins I

category: Website | course: SQL | difficulty:

All the queries and fancy keywords we’ve used thus far, were mainly used on single tables. Sure, we learnt how to combine select statements to get much larger data sets from multiple tables. That’s only one of many ways, however, to create queries that work with multiple tables. Instead of mixing everything together, it’s far more common to only combine data sets on rows that have something in common, or only include extra entries based on a certain condition, and so on.

For working with many entries from many different places, we use joins. Many different types of joins exist, and I will explain them from simplest to most complex.

Cross Join

The cross join simply joins every row from one table, with every row from another table. If you remember anything from mathematics, you might know this as a Cartesian product, commonly denoted with a cross (×). Not surprisingly, it’s also called the Cartesian join.

So, if you have one table with 10 rows and 2 columns, and another with 5 rows and 2 columns, the result will be a data set with 50 rows and 4 columns.

SQLCrossJoin

To create a cross join, use one of these syntaxes

SELECT * FROM table1, table2 SELECT * FROM table1 CROSS JOIN table2
-- Say we're writing a program that randomly combines sentences, just for fun
-- CONCAT simply puts the strings together, don't worry about it for now
SELECT CONCAT(front, back) AS sentence

FROM front_sentences 

CROSS JOIN back_sentences
front
Mike went swimming...
He borrowed a duck...
back
because he was lonely.
because it was his mission.
sentence
Mike went swimming...because he was lonely.
Mike went swimming...because it was his mission.
He borrowed a duck...because he was lonely.
He borrowed a duck...because it was his mission.

Usually, this join is a bit over the top – you’ll likely only need a small set of those rows you’re selecting. Either way, it’s the simplest join there is if you want to play around with joins.

Inner Join

An inner join is essentially a cross join with conditions added. It doesn’t combine all rows, but only rows that fulfil a certain condition. So, it selects a subset of the cross join, or an inner part of the full data set – hence the name.

SQLInnerJoin

An inner join can be performed implicitly, which means you’re playing with rows from multiple tables at the same time and trusting SQL to do the right thing, or explicitly, by using a keyword.

Implicit

One powerful feature of SQL, is that we can easily access anything from any table within the database. This is done with the dot notation:

someTable.someColumn

We can use this notation to work with data from multiple tables at the same time, without having to explicitly join anything. All we need to do is include a WHERE clause, using the dot notation, that states which rows we should combine.

-- Combine comments with information about the user
SELECT users.username AS usn, 
       comments.comment AS cmt
FROM users, comments

WHERE usn = comments.user_id
idusername
1Panda
2Ginty
3Juan
user_idcomment
1I like to move it, move it
3This is a test comment.
4Dragons are awesome!
usncmt
PandaI like to move it, move it
JuanThis is a test comment.

This notation can also be used at any other place within the query, because it isn’t really any different from the column names we’ve been using thus far. It simply selects a column from a foreign table, nothing more, nothing less.

Explicit

The alternative syntax for inner joins, which is less flexible but often makes the query more readable, is:

SELECT __ FROM table1 INNER JOIN table2 ON conditions

Note that we must still use the dot notation – it’s just that the join itself and the corresponding condition have their own syntax.

-- This does exactly the same as the previous example
SELECT users.username AS usn, 
       comments.comment AS cmt
FROM users
INNER JOIN comments
ON usn = comments.user_id
usncmt
PandaI like to move it, move it
JuanThis is a test comment.

Biased Joins

We’ve seen the cross join that selects everything, and the inner join that only selects rows if they match a certain condition in both tables. That’s nice and fair, but what if we want the query to be biased, and select results from one table differently than from the other? We can use what I call biased joins.

SQLBiasedJoins

Left Join

The left join selects all rows from the first table, but only the rows that match the conditions from the second table. Therefore, there will be rows from the first table that don’t have a matching row in the second. If that’s the case, it fills in NULL values for those columns. Use one of these syntaxes:

SELECT __ FROM table1 LEFT JOIN table2 SELECT __ FROM table1 LEFT OUTER JOIN table2
-- Using the same tables as previous examples, a left join instead of inner join would give these results
SELECT users.username AS usn, 
       comments.comment AS cmt
FROM users

LEFT JOIN comments
ON usn = comments.user_id
usncmt
PandaI like to move it, move it
Ginty
JuanThis is a test comment.

Right Join

The right join selects only the rows that match the conditions from the first table, but all rows from the second table. So, it’s a reversed left join. Use one of these syntaxes:

SELECT __ FROM table1 RIGHT JOIN table2 SELECT __ FROM table1 RIGHT OUTER JOIN table2
-- If we turn around the table order, a right join would do exactly the same as the previous left join
SELECT users.username AS usn, 
       comments.comment AS cmt
FROM comments

RIGHT JOIN users
ON usn = comments.user_id
usncmt
PandaI like to move it, move it
Ginty
JuanThis is a test comment.

Full Join

The full join is a combination of the left and right join.

What’s the difference with a cross join or inner join? The full join uses every row from the first table, and every row from the right table (like a cross join). However, if it finds two rows that match the provided conditions, it will combine them (just like an inner join). If it can’t find a matching row from the other table, it will fill in NULL values for all the other columns (like left and right joins).

So, it will include every row from the first table only once, and every row from the second table only once. And, if possible, it will combine rows.

SELECT __ FROM table1 FULL OUTER JOIN table2
-- Combine comments with information about the user
SELECT users.username AS usn, 
       comments.comment AS cmt
FROM users

FULL JOIN comments
ON usn = comments.user_id
usncmt
PandaI like to move it, move it
Ginty
JuanThis is a test comment.
Dragons are awesome!
CONTINUE WITH THIS COURSE
Do you like my tutorials?
To keep this site running, donate some motivational food!
Crisps
(€2.00)
Chocolate Milk
(€3.50)
Pizza
(€5.00)