I have previously looked at the core SQL syntax to form the majority of basic functions. Today I will look at JOINS and UNIONS in SQL.

JOINS

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. So first up what are the types of joins available:

INNER Join returns all rows where there is at least one match in both tables
OUTER returns all rows from both tables and merges matches together
LEFT returns all rows from the left table and any matching records from the right table
RIGHT returns all records in the right table and any matching records in the left table

Example using an Join (INNER)

Select column_name
from table1 as t1
inner join table2 as t2
on t1.column_name=t2.column_name;

For example, if I want to join 2 tables on Olympic Games and Events Data.

I might use:

select oe.*, og.[Year]
from Olympics_Events oe
join Olympics_Games og on oe.Games = og.Games
where og.year > 1952

Here I am looking for all the columns in the events table and just the year in the games table so its joining on the games field. I only select the year column from the games table to prevent a duplicate ‘Games’ Column.

The other joins work pretty much the same but with a slightly different syntax and results may vary.

UNIONS

The two types of union are UNION and UNION ALL. The main difference between the two is that UNION will remove duplicates  etween the two tables and UNION ALL will keep them.

There are a few rules between UNIONS:

  • Every SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in every SELECT statement must also be in the same order

 

UNION use the following syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL is the same but different:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

As long as the tables have the same columns then it will append them together with all records. There are ways around them not having the same columns but we will leave that for another day.

 

Ok so thats it. A crash course in SQL foundations.

 

 

 

 

 

 

 

 

The Data School
Author: The Data School