How to Join Multiple Tables in SQL?

sql join multiple tablesSometimes you ponder which SQL syntax to use to combine data that spans over multiple tables. JOIN is a syntax often used to combine and consolidate one or more tables. Tables are joined two at a time making a new table which contains all possible combinations of rows from the original two tables.

As a developer you may have different approaches towards handling database queries. MySQL Database For Beginners elaborates on how to join tables. Here are some of the more frequently used methods for consolidating queries on multiple tables into a single statement. While some of these options may affect performance, may increase or decrease the processing time, may pop up errors, but gradually through practice and experience you will know when to use which type of query.

If you need a refresher course on SQL coding and syntax, then SQL Database for Beginners and Learn SQL in 6 days are great courses to check out. They will guide you on how to perform operations on databases, how to perform Insert and Deletes in MS SQL, and how to insert data into tables. If you are new to writing SQL queries and want to learn how to build applications or generate business reports using SQL, then Introduction to SQL Training is a perfect match for you.

Methods to Join Multiple Tables

One simple way to query multiple tables is to use a simple SELECT statement. You can call more than one table by using the FROM clause to combine results from multiple tables.

Syntax:

SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = table2.column1;

The UNION statement is another way to return information from multiple tables with a single query. The UNION statement allows you to perform queries against several tables and return the results in a consolidated set, as in the following example:

Syntax:

SELECT column1, column2, column3 FROM table1

UNION SELECT column1, column2, column3 FROM table2;

This will return a result set with three columns containing data from both queries. By default, the UNION statement will omit duplicates between the tables unless the UNION ALL keyword is used. UNION is helpful when the returned columns from the different tables don’t have columns or data that can be compared and joined, or when it prevents running multiple queries and appending the results in your application code. As with subqueries, UNION statements can create a heavy load on your database server, but for occasional use they can save a lot of time.

SQL JOIN

With SELECT and UNION, some databases may have a limit on the number of tables that can be handled. That’s when it is a good idea to use the SQL JOIN statement to join two or more tables.

Types of SQL JOINS

Following are the different types of SQL joins:

  • SQL INNER JOIN (or sometimes called simple join)
  • SQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
  • SQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
  • SQL FULL OUTER JOIN (or sometimes called FULL JOIN)
  • SQL CROSS JOIN

SQL INNER JOIN:

Definition: This returns all rows from multiple tables where the join condition is met or returns the records where table1 and table2 intersect. An INNER JOIN will only return rows for which there is data in both the tables. It specifies the complete cross product of two tables.

Syntax:

SELECT columns

FROM table1

INNER JOIN table2

ON table1.column = table2.column;

SQL LEFT OUTER JOIN:

Definition: This type of join returns all rows from the LEFT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. This statement returns all records from table1 and only those records from table2 that intersect with table1.

Syntax

SELECT columns

FROM table1

LEFT [OUTER] JOIN table2

ON table1.column = table2.column;

SQL RIGHT OUTER JOIN:

Description: This type of join returns all rows from the RIGHT-hand table specified with the ON condition and only those rows from the other table where the join condition is met. In other words, this statement returns all records from table2 and only those records from table1 that intersect with table2.

Syntax

SELECT columns

FROM table1

RIGHT [OUTER] JOIN table2

ON table1.column = table2.column;

 SQL FULL OUTER JOIN:

Definition: This type of join returns all rows from the LEFT-hand table and RIGHT-hand table. Where the join condition is not met, nulls are inserted. The result after the JOIN operation will be all the records from both table1 and table2.

Syntax

SELECT columns

FROM table1

FULL [OUTER] JOIN table2

ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

SQL CROSS JOIN:

This will return all records where each row from the first table is combined with each row from the second table. A CROSS JOIN can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.

Syntax:

SELECT * FROM [TABLE 1] CROSS JOIN [TABLE 2]

OR

SELECT * FROM [TABLE 1], [TABLE 2]

Summary

Your success as a successful DBA lies in structuring and developing a properly designed and linked database, which will help you to retrieve information from as many tables as you want, specify retrieval conditions based on any data in the tables, and show the results in any order that you like. Also remember that an important factor about subqueries is performance. Each query is processed separately in full before being used as a resource for your primary query. So creative use of JOIN statements may provide better results with a lesser lag time.

Courses like SQL Server Essentials: What you should know! and Practical SQL Skills from Beginner to Intermediate will help you learn key SQL skills to boost your career.