Subquerying with semi joins and anti joins

Unire i dati 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.

Unire i dati in SQL

Additive joins

SELECT *
FROM left_table
INNER JOIN right_table
ON left_table.id = right_table.id;
Unire i dati 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.

Unire i dati 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.

Unire i dati 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.

Unire i dati 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.

Unire i dati 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          |
Unire i dati in SQL

Building on our semi join

SELECT country
FROM states
WHERE indep_year < 1800;

|----------|
| country  |
|----------|
| Portugal |
| Spain    |
|----------|
Unire i dati 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      |
|-------------------------|-----------|-------------|
Unire i dati 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.

Unire i dati 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.

Unire i dati 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    |
Unire i dati in SQL

Let's practice!

Unire i dati in SQL

Preparing Video For Download...