Combining Data in SQL : Join & Union

SQL, or Structured Query Language, is used to interact with multiple tables of data. Coming from industry where excel was used heavily and daily, I found myself being intrigued by how similar SQL seemed to be to my beloved ❤ pivot tables.

JOIN

This operator combines two or more tables of data using columns and a condition.

Below is the syntax for JOIN:

SELECT *
FROM Table1
INNER JOIN Table2 
ON table1.id = table2.id;           #Condition

Let’s look at below two tables of cats and owners.

Here, join can be used to create a new table by matching Cats table’s owner_id and Owners table’s id. Note how we are using “Inner Join” and how the condition of matching the ID’s are noted after “ON”.

SELECT Cats.name, Cats.breed, Owners.name
FROM Cats
INNER JOIN Owners
ON Cats.owner_id = Owners.id;

This inner join creates a new table containing only the data existing in both tables. In our example with cats + owners Lil’Bub is dropped because owner_id is null.

There are multiples ways to use join to combine data: using left join, right join, inner join and full join. Left join adds all data from where the left data is called, or the first table used in the syntax, with the data that exists in both tables. Inner join only shows the coinciding data between the two tables. Full join shows all the data in both tables.

https://www.thinktanks.co.za/joins-in-sql/

UNION

This operator combines tables of data using rows. Union is often used to sort same data from two separate tables into one.

To use union below conditions have to be met:

1. Each of the select statements used must have same number of conditions
2. Columns required must have similar type of data
3. Columns selected must be in the same order

Below is UNION’s syntax:

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

Let’s take example of a school keeping track of 2 classroom full of students from different cities. Union will allow the school to combine cities from both tables and create a city table.

If we want to combine the cities of both tables we can use UNION to get back a list of all cities, without duplicates. We can also use UNION ALL to get back all results, including duplicates.

EXCEPT returns left table data without coinciding data and INTERSECT is used to create table with only the coinciding data.

Here’s a quick chart!

https://www.essentialsql.com/learn-to-use-union-intersect-and-except-clauses/

Fun Fact from blog post, “6 Reasons Why You Should Learn SQL”:

According to the job posting website Indeed.com, there are more SQL programming jobs (in 2016) than any other type of programming language, including Java, JavaScript, C+, Python, C++, and PHP.

Happy mixing!

Sources:

6 Reasons Why You Should Learn SQL

How do I combine results from more than one table? - Essential SQL

Introduction to Database Joins - Essential SQL

SQL UNION Operator

Sql Joins - Learn.co