Chapter 7: Selecting Data Part 3

 

We’ve covered quite a few data selection concepts in the last two chapters. Those two chapters focused mainly on selecting data from a single table.

 

In this chapter, we’ll learn to select and combine data from one or more tables.

 

Joins

 

Let’s start with joins.

 

Like the name suggests, a join is used to join data from different tables based on a related column between the tables.

 

The syntax for joining two tables is:

 

SELECT [table_names.]columns_names_or_other_information

FROM

left_table

JOIN / INNER JOIN / LEFT JOIN / RIGHT JOIN

right_table

ON

left_table.column_name = right_table.column_name;

 

There are three main types of joins in mySQL: inner join, left join and right join. These are represented by the Venn diagrams below: To demonstrate the difference between them, let’s consider the following two tables:

 

Here, we have two tables called one and two.

 

You can see that column A in table one shares some common values with column C in table two. We can join the two tables using these two columns.

 

If we do an inner join, we write

 

SELECT A, C, one.B AS 'one B', two.B AS 'two B'

FROM

one

INNER...

Unions

 

Now that we understand how joins work, let’s move on to unions. This is a relatively easy concept.

 

The UNION keyword is used to combine the results of two or more SELECT statements. Each SELECT statement must have the same number of columns. The syntax is:

 

SELECT_statement_one

UNION

SELECT_statement_two;

 

The column names from the first SELECT statement will be used as the column names for the results returned.

 

Let’s look at an example.

 

For the employees table, we can do a UNION as follows:

 

SELECT em_name, salary FROM employees WHERE gender = 'M'

UNION

SELECT em_name, years_in_company FROM employees WHERE gender = 'F';

 

We’ll get the following output:

 

 

The first 5 rows are from the first SELECT statement while the last 4 are from the second statement.

 

Note that by default, the UNION keyword removes any duplicates from the result. If you...