Traducciones al Español
Estamos traduciendo nuestros guías y tutoriales al Español. Es posible que usted esté viendo una traducción generada automáticamente. Estamos trabajando con traductores profesionales para verificar las traducciones de nuestro sitio web. Este proyecto es un trabajo en curso.
Create a Linode account to try this guide with a $ credit.
This credit will be applied to any valid services used during your first  days.

Traditionally, you pull data from two or more tables using a WHERE clause in a query. But in a relational database system (RDBMS), this can be achieved using a single SELECT query. This is the true power of relational database systems. In this guide, you learn about SQL Joins, a powerful way to compare and select rows and tables.

What is a SQL Join?

In SQL, a join clause extends the capability of comparing and selecting rows from tables. It uses an algebraic process of combining rows from two or more tables based on a related column in those tables. By the ANSI-standard SQL definition, there are five types of Joins –Cross Joins, Inner Joins, Left (Outer) Joins, Right(Outer) Joins, and Full (Outer) Joins. These Joins are implemented across all relational database systems and are covered in the sections below.

Note
Joins can be performed on any number of tables in a given query. For brevity and clarity, this guide discusses Joins applied to two tables.

This guide uses two tables, Employees and Address, respectively, to demonstrate SQL Joins. Each of these tables contain the following column definitions and data:

  • Employees Table

    EmployeeIdEmployeeName
    1John
    2Mary
    3Robert
  • Address Table

    IdState
    1New York
    2New Jersey
    3Idaho
    4Hawaii
    Note
    Unless mentioned otherwise, all the commands in this guide work well on both MySQL and PostgreSQL databases.

SQL Cross Joins

Also known as a Cartesian Join, Cross Joins occur when you specify multiple tables as a source for your SELECT column list. In this case, you leave out the WHERE clause join expression to match rows on. The result set contains a row for every combination of rows between the tables. In a two-table scenario, every row in one table is paired with every row of the other table. The resulting product is known as the Cartesian Product of the two tables. The syntax for a Cross Join is the following:

(# Rows in Table A) TIMES (# of Rows in Table B)
Note

In set theory, the Cartesian Product is a multiplication operation that generates all ordered pairs of the given sets. For example, consider set A with elements {a,b} and set B with elements {1,2,3}. The Cartesian Product of A and B is denoted by AxB and the result is the following:

AxB ={(a,1), (a,2), (a,3), (b,1), (b,2), (b,3)}

The SQL syntax for a Cross Join is as follows:

SELECT ColumnName_1,
       ColumnName_2,
       ColumnName_N
FROM [Table_1]
     CROSS JOIN [Table_2]

From the above syntax, Column_1, Column_2, Column_N represent the columns in a table, and the CROSS JOIN clause serves to combine the two tables, Table_1 and Table_2. From the example tables above, if you need to perform a Cross Join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
CROSS JOIN Address

The output of the above SQL code resembles the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| John         |   New Jersey |
| John         |   Idaho      |
| John         |   Hawaii     |
| John         |   New York   |
| Mary         |   New York   |
| Mary         |   New Jersey |
| Mary         |   Idaho      |
| Mary         |   Hawaii     |
| Robert       |   New York   |
| Robert       |   New Jersey |
| Robert       |   Idaho      |
| Robert       |   Hawaii     |
+------------+----------------+

SQL Inner Join

An Inner Join returns rows that have matching values in both tables. If there are no matching records, then no rows are returned in the results.

The SQL syntax for Inner Join is as follows:

SELECT ColumnName_1,
       ColumnName_2,
       ColumnName_N
FROM Table_1
INNER JOIN Table_2
ON Table_1.key = Table_2.key;

In the example above, key is the respective key of the tables. If you need to perform an inner join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
INNER JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code resembles the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
+------------+----------------+

SQL Left (Outer) Join

A Left Join returns a complete set of rows from the left table along with the matching rows from the right table. If there are no matching records, then NULL values are returned from the right table.

Note
Some relational database implementations use the keywords “Left Outer Join”, as opposed to “Left Join”, but they are functionally equivalent.

The SQL syntax for Left Join is as follows:

SELECT * FROM Table_1
LEFT JOIN Table_2
ON Table_1.key = Table_2.key

In the example above, key is the respective key of the tables. If you need to perform a left join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
LEFT JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code is as follows:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
| Robert       |   NULL       |
+------------+----------------+

SQL Right (Outer) Join

A Right Join returns a complete set of rows from the right table and the matching rows from the left table. This is also known as a Right Outer Join. If there are no matching records, then NULL values are returned from the right table, for the affected rows in the left table.

Note
Some relational database implementations use the keywords “Right Outer Join”, as opposed to “Right Join”, but they are functionally equivalent.

The SQL syntax for a Right Join is as follows:

SELECT * FROM Table_1
RIGHT JOIN Table_2
ON Table_1.key = Table_2.key

From the above code, key is the respective key of the tables. If you need to perform a right join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
RIGHT JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code is the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
| NULL         |   Idaho      |
| NULL         |   Hawaii     |
+------------+----------------+

SQL Full (Outer) Join

A Full Join returns all rows from the left table, all rows from the right table. This is also known as also known as a Full Outer Join. A Full Join also returns all matching records from both tables where available. If there are no matching records, then NULL values are returned from the left table. It also returns NULL values from the right table.

Note
Some relational database implementations use the keywords “Full Outer Join”, as opposed to “Full Join”, but they are functionally equivalent.

The SQL syntax for Full Join is as follows:

SELECT * FROM Table1
FULL JOIN Table2
ON Table1.key = Table2.key

In the above code, key is the respective key of the tables. If you need to perform a full join on Employees and Address tables, use the following SQL code:

SELECT EmployeeName, State
FROM Employees
FULL JOIN Address
ON Employees.EmployeeId = Address.Id

The output of the above SQL code is the following:

+--------------+--------------+
| EmployeeName | State        |
+---------------+-------------+
| John         |   New York   |
| Mary         |   New Jersey |
| Robert       |   NULL       |
| NULL         |   Idaho      |
| NULL         |   Hawaii     |
+------------+----------------+
Note
During Join calculations, if you compare table data with NULL values, they do not match one another. Hence, NULL values are only returned as part of Join results and are ignored during Join calculations.

Performance Comparison of SQL Joins

Considering the above example tables, the Inner Join is typically the fastest of the five Join clauses in terms of database performance. The Left Join and the Right Join are the next fastest depending on the size of the two tables. The Full Join is typically slower than the Left Join or the Right Join. The Cross Join, reliant on the Cartesian product of the two tables, is typically the slowest in terms of database performance. The specified performance hierarchy may differ depending on the table column length, column datatype, and key definitions.

Conclusion

The use of SQL Joins extends the functionality of being able to compare table rows, over traditional WHERE clause queries. Joins are a valuable mechanism to apply algebraic logic to two or more tables.

To learn more about SQL, see our guides on SQL data types, grouping and totaling, and SQL user management security.

This page was originally published on


Your Feedback Is Important

Let us know if this guide was helpful to you.


Join the conversation.
Read other comments or post your own below. Comments must be respectful, constructive, and relevant to the topic of the guide. Do not post external links or advertisements. Before posting, consider if your comment would be better addressed by contacting our Support team or asking on our Community Site.
The Disqus commenting system for Linode Docs requires the acceptance of Functional Cookies, which allow us to analyze site usage so we can measure and improve performance. To view and create comments for this article, please update your Cookie Preferences on this website and refresh this web page. Please note: You must have JavaScript enabled in your browser.