SQL JOIN

When we need to combine multiple tables and analyse their data simultaneously, we have to write an SQL query using a JOIN.
A friend of mine, even after researching how to use JOIN, said he was still having trouble writing queries with JOIN clauses.
Because of that, I decided to create a post that’s simple and straightforward, so he can clearly understand how to perform queries using JOIN.

Let’s suppose I have two tables:

Table A

CREATE TABLE A (
  id serial primary key,
  name varchar(255)
);

Table B

CREATE TABLE B (
  id serial primary key,
  name varchar(255),
  a_id int
);

Next, let's insert data into them:

Table A

INSERT INTO A (name) VALUES ('A1'), ('A2'), ('A3');

Table B

INSERT INTO B (name, a_id) VALUES ('B1', 1), ('B2', 2), ('B3', null);

This way, we’ll have our tables with their respective data inserted:

Table A

id	name
1	A1
2	A2
3	A3

Table B

id	name	a_id
1	B1	1
2	B2	2
3	B3

Note that both tables share an ID column.

Syntax

SELECT 
   table1.column_name
   table2.column_name
FROM table1 t1 -- choose the main table
JOIN table2 t2 -- indicates the table that will be combined
  ON t1.key = t2.key; -- "match" condition between the tables

There are a few things to keep in mind when writing a JOIN in SQL. It’s necessary to specify which columns should be displayed. Since our example is very short and I want to make it clear what each JOIN returns, I’ll use * to make it easier to visualise. However, in more complex cases, we specify only the data we want to show in the SELECT statement.
A JOIN can be an INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN.
We use the ON clause to specify which tables we are joining and how the tables are related. We need to explicitly state in the SQL query that the id column of table A corresponds to the a_id column of table B.

Now, let’s run SQL statements that will select records with matching values in both tables.

INNER JOIN
QUERY:

SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1     1      B1      1
 2      A2     2      B2      2

The INNER JOIN returns rows where the condition A.id = B.a_id is true in both tables. B3 → a_id has no match. Since there’s no corresponding row, B3 isn't included in the INNER JOIN.

LEFT JOIN
QUERY:

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1     1      B1      1
 2      A2     2      B2      2
 3      A3    null   null    null

In a LEFT JOIN, all rows from table A are retained. When there’s no matching row in B, the fields from B are NULL.

RIGHT JOIN
QUERY:

SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1     1      B1      1
 2      A2     2      B2      2
null   null    3      B3     null

In a RIGHT JOIN, all rows from table B are retained. When there’s no matching row in A, the fields from A are NULL.

FULL OUTER JOIN
QUERY:

SELECT * FROM A FULL OUTER JOIN B ON A.id = B.a_id;

OUTPUT:

a.id  a.name  b.id  b.name  b.a_id
 1      A1      1     B1       1
 2      A2      2     B2       2
null   null     3     B3      null
 3      A3    null    null    null

The FULL OUTER JOIN (or OUTER JOIN) returns all rows from both A and B, with NULL where there is no match, and also includes rows that don’t have a corresponding match, such as the row A3, which had no match, and the row B3, which also had no match.

Understanding JOINs is essential for efficiently working with relational databases. When we clearly understand the different types and how tables relate to one another, we can write queries better.