Joining Data in SQL

CodingDatabases One can analyze data from multiple tables simultaneously by using one of the four JOIN operators, or the UNION, INTERSECT and EXCEPT operators.

A JOIN operation appends new columns to the results table consists of three parts:

1. SELECT - specifies the columns to return
2. table1 JOIN table2 - the names of the tables being joined
3. ON - specifies the column common to both tables

The UNION/INTERSECT/EXCEPT operations append/remove rows from two resulting queries where the the columns match in number, data types, and order:

1. UNION - concatenates the results of two queries, removing duplicates (only get unique rows)
2. UNION ALL - concatenates the results of two queries, including duplicates
3. INTERSECT - return the common rows from the two queries
4. EXCEPT - return unique rows from the first query but not in the second

A generic query might look like:

SELECT *
FROM table1
JOIN table2
ON table1.column = table2.column
	AND condition;

The four types of JOIN operations are

1. table1 INNER JOIN table2 - return only the rows with values in both tables
2. table1 LEFT JOIN - return all rows from table1 and impute missing data from table2 with NULL
3. table1 RIGHT JOIN table2 - return all rows from the table2 and impute missing data from table1 with NULL
4. table1 FULL OUTER JOIN table2 - return all rows from both tables and impute missing data from each with NULL (WARNING: can create duplicate rows)