How to Select From two Tables in MySQL

In many cases, you often need to get data from multiple tables in a single query. You can use JOIN clause to get data from multiple tables in your single SQL query. A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

images/articles/mysql/select-from-two-tables-in-mysql.jpg

For example,

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Types of SQL JOINs

There are four different types of the JOINs in SQL:

  1. (INNER) JOIN: Returns records that have matching values in both tables
  2. LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
  3. RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
  4. FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

1. Inner Join

The INNER JOIN keyword selects records that have matching values in both tables. This is the most common type of join.

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns.

2. Left Join

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

The LEFT JOIN keyword returns all records from the left table (table1), even if there are no matches in the right table (table2).

3. Right Join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

The RIGHT JOIN keyword returns all records from the right table (table2), even if there are no matches in the left table (table1).

4. Full Join

The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records. It can potentially return very large result-sets.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

The FULL OUTER JOIN keyword returns all the rows from the left table (tabl1), and all the rows from the right table (table2). If there are rows in "table1" that do not have matches in "table2", or if there are rows in "table2" that do not have matches in "table1", those rows will be listed as well.