A quick trip into SQL joins

Oswaldo Ferreira

Follow

Feb 25, 2017 · 3 min read

December 23, 2016

I think SQL is a language that every developer should master.

It’s pretty common to see frameworks wrapping SQL into the language where the framework was written on, and sometimes we have to take a step further, leaving those abstractions behind and taking closer to the real deal. Normally for performance or query complexity purposes.

What we’ll understand here is how SQL joins work. To achieve that we will create tables and insert data before finally visiting:

• INNER JOIN (or JOIN)
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

Side-note: All the commands below were tested on PostgreSQL. There are variations such as AUTO_INCREMENT for MySQL being SERIAL on PosgreSQL.

Before join’in (ahá), let’s create our tables:

CREATE table cities ( id SERIAL UNIQUE, name varchar(255) ); CREATE table events ( id SERIAL UNIQUE, title varchar(255), start_on date NOT NULL, city_id integer, FOREIGN KEY (city_id) REFERENCES cities (id) MATCH FULL );

Insert some data on cities and events:

INSERT INTO cities (name) VALUES ('Rio de Janeiro'); INSERT INTO cities (name) VALUES ('São Paulo'); INSERT INTO events (title, start_on) VALUES ('Christmas', '2016-12-25'); INSERT INTO events (title, start_on) VALUES (E'Father\'s', '2017-08-13'); INSERT INTO events (title, start_on, city_id) VALUES ('Carnival', '2016-02-28', 1);

And check our nicely inserted data:

SELECT * FROM cities; id | name ----+---------------- 1 | Rio de Janeiro 2 | São Paulo SELECT * FROM events; id | title | start_on | city_id ----+-----------+------------+--------- 2 | Christmas | 2016-12-25 | 3 | Carnival | 2016-02-28 | 1 4 | Father's | 2017-08-13 |

JOIN (INNER JOIN)

Perhaps the most common join form is the INNER JOIN (which has an alias as JOIN). It can be used when you want only the rows that match the ON statement.

SELECT c.name, e.title FROM events e JOIN cities c ON c.id = e.city_id;

Note that only rows where c.id = e.city_id matches are returned, excluding the rest.

name | title ----------------------+---------- Rio de Janeiro | Carnival

It’s good to reiterate that we’re returning a new table on the fly, and that result can also be used for another query. Let’s take this as an example:

SELECT * FROM events WHERE title = ( SELECT e.title FROM events e JOIN cities c ON c.id = e.city_id ); id | title | start_on | city_id ----+----------+------------+--------- 3 | Carnival | 2016-02-28 | 1

LEFT JOIN

Left joins can be used when you want a result set containing every row of the leftmost table on the query, AND, every row on the right table that satisfies the ON statement. Rows that DO NOT satisfy the ON statement will return NULL.

SELECT c.name, e.title FROM events e LEFT JOIN cities c ON c.id = e.city_id; name | title ----------------+----------- | Christmas Rio de Janeiro | Carnival | Father's

Note that every events table rows are returned (left table), and just the rows that match the ON statement on cities (right table) are returned.

RIGHT JOIN

The same rule explained above is applied to the RIGHT JOIN, but reversed.

SELECT c.name, e.title FROM events e RIGHT JOIN cities c ON c.id = e.city_id; name | title ----------------+---------- Rio de Janeiro | Carnival São Paulo |

Compare the RIGHT and LEFT join result set.

FULL JOIN

The FULL JOIN combines both tables, returning NULL where it does not match the ON clause:

SELECT c.name, e.title FROM events e FULL JOIN cities c ON c.id = e.city_id;name | title ----------------+----------- | Christmas Rio de Janeiro | Carnival | Father's São Paulo |

There’s a good cheat-sheet image for remembering those rules:

Remember to practice those queries on your own favorite relational database terminal session. Enjoy the ride!

Originally published at words.oswaldo.me.