Join Columns

We wish to join two tables based on specific columns.

This section is organized as follows:

This section is complemented by:

  • Join Types: where we cover how to the different join types and their meaning.
  • Join Special Cases: where we cover how to deal with special cases when performing a join operation.

One Column

We wish to join two tables based on one column.

Common Column Name

We wish to join two tables based on one column that have the same name in the tables to be joined.

In this example, we wish to join table_1 and table_2 based on a column named col_1 in both tables.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 USING (col_1);

Here is how this works:

  • We use the INNER JOIN operation in SQL to combine table_1 and table_2 based on the common column col_1.
  • We pass the common column name to USING clause which performs an equality comparison between the col_1 in table_1 and col_1 in table_2.
  • We give each table an alias using AS, in this example table_1 alias is t1 and table_2 alias is t_2. We recommend using table alias always when performing a join, so we can easily select the columns we wish to keep from each table.
  • We specify the list of columns we wish to keep in the SELECT statement. See Selecting By Name.
  • The output table will contain the rows that have the same value for col_1 in both table_1 and table_2 and all columns from table_1 and col_4 from table_2.
  • The same applies to other join types. See Join Types.

Different Column Name

We wish to join two tables based on one column that has a different name in either table.

In this example, we wish to join table_1 and table_2 based on col_1_x from table_1 and col_1_y in table_2.

SELECT t1.*,
       t2.*
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 ON t1.col_1_x = t2.col_1_y;

Here is how this works:

  • We use the INNER JOIN operation in SQL to combine table_1 and table_2 based on column col_1_x from table_1 and col_1_y from table_2.
  • We use the ON clause to specify the join condition which is t1.col_1_x = t2.col_1_y in this example.
  • We give each table an alias using AS, in this example table_1 alias is t1 and table_2 alias is t_2. We recommend using table alias always when performing a join, so we can easily select the columns we wish to keep from each table.
  • Since all columns have different names in both tables we can use the select all notation .* to select all columns in a table. See Selecting By Name.
  • The output table will contain the rows that have the same value in col_1_x and col_1_y and all columns from table_1 and table_2.
  • The same applies to other join types. See Join Types

Multiple Columns

We wish to join two tables based on multiple columns.

Common Columns Names

We wish to join two tables based on multiple columns that have the same name in the tables to be joined.

In this example, we wish to join table_1 and table_2 based on the columns named col_1 and col_2 in both tables.

SELECT t1.col_1,
       t1.col_2,
       t1.col_3,
       t2.col_4
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 USING (col_1, col_2);

Here is how this works:

  • This works similar to the One Column scenario above, except we pass the list of common column names to USING clause which performs an equality comparison between them.
  • The same applies to other join types. See Join Types.

Different Columns Names

We wish to join two tables based on multiple columns, while each of the join columns has a different name in either table.

In this example, we wish to join table_1 and table_2 based on col_1_x and col_2_x from table_1 and col_1_y and col_2_y from table_2.

SELECT t1.*,
       t2.*
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 ON
            t1.col_1_x = t2.col_1_y AND
            t1.col_2_x = t2.col_2_y;

Here is how this works:

  • This works similarly to the One Column: Different Column Name scenario above except specify multiple join conditions in the ON clause. We use AND to combine the join conditions since we want all conditions to be met.
  • The output table will contain the rows that have the same value for all the specified join columns. All columns from both tables will be in the output table.

Extension: Multiple Join Conditions

We wish to join two tables based on multiple columns and using multiple types of join conditions.

In this example, we wish to combine rows from table_1 and table_2 where:

  • col_1_x and col_2_x have the same value in both tables
  • OR col_3_x from table_1 is bigger than col_3_y in from table_2.
SELECT t1.*,
       t2.*
FROM table_1 AS t1
         INNER JOIN table_2 AS t2 ON
            t1.col_1_x = t2.col_1_y OR
            t1.col_3_x > t2.col_3_y;

Here is how this works:

  • This works similarly to the Multiple Columns scenario above except use OR to separate the multiple join conditions in the ON clause.
  • On a high level, ON clause can take any conditional statement and will combine the two rows if the statement is true. General Operations: Conditional.
SQL
I/O