Subquerying with semi joins and anti joins

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

Calling all joins

Diagram for an INNER JOIN on the id field

A diagram showing two tables: left_table and right_table. The result of joining the two queries is shown on the right, returning only the records that matched on the id column for both tables.

Joining Data in SQL

Additive joins

SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;
Joining Data in SQL

Additive joins

A diagram explaining an additive join. On the left side, two tables, left_table and right_table are displayed. On the right side, the result of an INNER JOIN using an id column on the two tables is shown. Fields with different names are added with their original names. Since the date field appears in both tables, it is added twice.

Joining Data in SQL

Semi join

A semi join chooses records in the first table where a condition is met in the second table.

A diagram showing two tables, left_table and right_table, that will be used to demonstrate a semi join. left_table contains two columns, id and col1. right_table contains only one column, col2.

Joining Data in SQL

Semi join

A diagram showing two tables, left_table and right_table, that are being used to demonstrate a semi join. col2 will be used to filter col1. Records in left_table where col1 does not find a match in col2 have been faded out.

Joining Data in SQL

Semi join

A diagram showing two tables, left_table and right_table. The result of a semi join on both tables is shown on the right. Only records in left_table where col1 finds a match in col2 have been returned. These correspond to ids 2 and 3.

Joining Data in SQL

Kicking off our semi join

SELECT country, continent, president
FROM presidents;

| country  | continent     | president               |
| -------- | ------------- | ----------------------- |
| Egypt    | Africa        | Abdel Fattah el-Sisi    |
| Portugal | Europe        | Marcelo Rebelo de Sousa | 
| USA      | North America | Joe Biden               |
| Uruguay  | South America | Luis Lacalle Pou        |
| Pakistan | Asia          | Asif Ali Zardari        |
| Chile    | South America | Gabriel Boric           |
| India    | Asia          | Droupadi Murmu          |
Joining Data in SQL

Building on our semi join

SELECT country
FROM states
WHERE indep_year < 1800;

|----------|
| country  |
|----------|
| Portugal |
| Spain    |
|----------|
Joining Data in SQL

Finish the semi join (an intro to subqueries)

SELECT president, country, continent
FROM presidents
WHERE country IN
    (SELECT country
     FROM states
     WHERE indep_year < 1800);

|-------------------------|-----------|-------------|
| president               | country   | continent   |
|-------------------------|-----------|-------------|
| Marcelo Rebelo de Sousa | Portugal  | Europe      |
|-------------------------|-----------|-------------|
Joining Data in SQL

Anti join

A diagram showing two tables, left_table and right_table, that are being used to demonstrate an anti join. col2 will be used to filter col1. Records in left_table where col1 does not find a match in col2 have NOT been faded out.

Joining Data in SQL

Anti join

A diagram showing two tables, left_table and right_table. The result of an anti join on both tables is shown on the right. Only records in left_table where col1 does not find a match in col2 have been returned. These correspond to ids 1 and 4.

Joining Data in SQL

An anti join with the presidents

SELECT country, president
FROM presidents
WHERE continent LIKE '%America' 
    AND country NOT IN
        (SELECT country
         FROM states
         WHERE indep_year < 1800);

| country  |  president       |
| -------- | ---------------- | 
| Uruguay  | Luis Lacalle Pou |
| Chile    | Gabriel Boric    |
Joining Data in SQL

Let's practice!

Joining Data in SQL

Preparing Video For Download...