Multiple Tables

For efficiently storing data, related information is often spread across multiple tables. Some queries for working with multiple tables:

JOIN

Combines rows from different tables if the join condition is true.

e.g:

SELECT *
FROM table1
JOIN table2
  ON table1.target_col = table2.target_col;

Joins can be:

Inner joins

discards unmatching rows between joined tables, or

Left joins

Keep rows from first table whether or not there’s a matching row in second table.

There’s also:

CROSS JOINs

which combines all rows of one table with all rows of another, and there’s

UNION

which basically stacks one dataset on top of another provided that:

• the tables have same number of columns, and
• the columns have the same data types in the same order as the first table.

Besides those, there’s the handy …

WITH

which allows for definition of one or more temporarry tables which can be used in a final query.

Also worth pointin out are:

Primary key

which is a column that serves a unique identifier for the rows in the table, and

Foreign key

which is a column that contains the primary key to another table.