JOIN

It's rare for all the data to be stored in one table. Analysts usually need to merge tables; hence the JOIN operator.

There are two ways to join tables: the INNER and OUTER join.

The INNER join returns only those rows that have matching values from table to table (the tables' intersection).

The OUTER join retrieves all the data from one table and adds data from the other one when there are matching rows.

INNER JOIN

INNER JOIN selects only data for which the join conditions are met. The order in which the tables are joined does not affect the final result.

Here's a sample query with INNER JOIN:

COPYsql
1SELECT --listing only the necessary fields
2 TABLE_1.field_1 AS field_1,
3 TABLE_1.field_2 AS field_2,
4 ...
5 TABLE_2.field_n AS field_n
6FROM
7 TABLE_1
8INNER JOIN TABLE_2 ON TABLE_2.field_1 = TABLE_1.field_2;

Let's take a closer look at the syntax:

• INNER JOIN is the name of the joining method. Then comes the name of the table to be joined to the table the FROM block.
• ON precedes the join condition: TABLE_2.field_1 = TABLE_1.field_2. This means that only the table rows that match this condition will be joined. In our case the condition is that field_1 from the second table matches field_2 from the first.

Since fields in different tables can have the same names, they are referred to by both table name and field name. First comes the table name, then the field: TABLE_1.field_1.

Outer Join

There are two types of OUTER JOIN:

• LEFT OUTER JOIN
• RIGHT OUTER JOIN

We'll give these methods short names: LEFT JOIN and RIGHT JOIN.

LEFT JOIN

LEFT JOIN will select all the data from the left table together with the rows from the right table that match the join condition. RIGHT JOIN will do the same, but for the right table.

Here's the syntax of a statement with LEFT JOIN:

COPYsql
1SELECT
2 TABLE_1.field_1 AS field_1,
3 TABLE_1.field_2 AS field_2,
4 ...
5 TABLE_2.field_n AS field_n
6FROM
7 TABLE_1
8LEFT JOIN TABLE_2 ON TABLE_2.field = TABLE_1.field;

As with INNER JOIN queries, the table name is indicated for each field. Note that with OUTER JOIN the order in which tables are listed is significant.

RIGHT JOIN

RIGHT JOIN is LEFT JOIN's twin. But unlike its sibling, it takes all the data from the right table, and the matching rows from the left table.

Here's what a RIGHT JOIN query looks like:

COPYsql
1SELECT
2 TABLE_1.field_1 AS field_1,
3 TABLE_1.field_2 AS field_2,
4 ...
5 TABLE_2.field_n AS field_n
6FROM
7 TABLE_1
8RIGHT JOIN TABLE_2 ON TABLE_1.field = TABLE_2.field;

Joining Multiple Tables

Here's the syntax of a query that uses INNER JOIN several times:

COPYsql
1SELECT --listing only the necessary fields
2 TABLE_1.field_1 AS field_1,
3 TABLE_1.field_2 AS field_2,
4 ...
5 TABLE_3.field_n AS field_n
6FROM
7 TABLE_1
8INNER JOIN TABLE_2 ON TABLE_2.field = TABLE_1.field
9INNER JOIN TABLE_3 ON TABLE_3.field = TABLE_1.field;

We'll join the second table, then the third, to the first.

Joining Statements

The UNION and UNION ALL operators bring together data from tables. The syntax is as follows:

COPYsql
1SELECT
2 column_name_1
3FROM
4 table_1
5UNION --( or UNION ALL)
6SELECT
7 column_name_1
8FROM
9 table_2;

Here two SELECT - FROM statements are separated by the UNION command.

Here are the conditions that must be met for a UNION to work:

• The first and second tables must match with respect to the number of columns selected and their data types
• Fields must be in the same order in the first and second tables.

UNION avoids duplicating rows when it generates a table.