How to write SQL statements that connect 3 tables

SQL statements join three tables: Use the INNER JOIN clause to specify the join conditions between two tables You can join any number of tables Make sure that using the appropriate join type, the join condition must specify the columns that exist in both tables and have data type compatible.

SQL statements that join three tables

In SQL, multiple tables can be joined using the JOIN clause. The syntax for joining three tables is as follows:

<code class="sql">SELECT column_list FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name INNER JOIN table3 ON table2.column_name = table3.column_name;</code>

in:

• SELECT column_list: List of columns to be selected.
• FROM table1: The first table to be connected.
• INNER JOIN: Connection type, specifying the join conditions of two tables.
• ON table1.column_name = table2.column_name: Join condition, specifying the matching row between two tables.
• INNER JOIN table2 ON table2.column_name = table3.column_name: The second join condition, specifying the matching row between the second table and the third table.

Example

Suppose we have three tables:

• Employees: Contains employee information (ID, name, department)
• Departments: Contains department information (ID, name)
• Projects: Contains project information (ID, name, department ID)

To query each employee's name, department name, and current project name, we can use the following SQL:

<code class="sql">SELECT Employees.Name, Departments.DeptName, Projects.ProjName FROM Employees INNER JOIN Departments ON Employees.DeptID = Departments.DeptID INNER JOIN Projects ON Departments.DeptID = Projects.DeptID;</code>

Things to note

• The JOIN clause can join any number of tables.
• Use the appropriate connection type (INNER JOIN, LEFT JOIN, RIGHT JOIN, etc.) to ensure you get the desired results.
• The join condition must specify at least one column that exists in both tables and is compatible with the data type.

The above is the detailed content of How to write SQL statements that connect 3 tables. For more information, please follow other related articles on the PHP Chinese website!