Let's talk about SQL table connections

This article brings you relevant knowledge about SQL, which mainly introduces the relevant content about table connections. SQL Server supports multiple connections, including inner joins, left joins, and right joins. Join, cross join and full outer join, let's take a look at them together, I hope it will be helpful to everyone.

Recommended study: "SQL Tutorial"

SQL table connection

In a relational database, data Distributed across multiple logical tables. To obtain a complete and meaningful data set, you need to use joins to query the data in these tables. SQL Server supports a variety of connections, including inner joins, left joins, right joins, cross joins and full outer joins.

1. Inner join

The inner join clause is used to query data from two or more related tables. The syntax of the INNER JOIN clause:

SELECT p.product_name, p.list_price ,c.category_nameFROM production.products p 
INNER JOIN production.categories c ON c.category_id = p.category_id

Note: The field to be queried must be preceded by the table name or table alias, such as: p.product_name
The one after FROM is the main table, and then the table following INNER JOIN is the table to be connected. The main table and the join table are compared to find all rows that meet the conditions. If the calculation result of the connection predicate is true, the main table and the connection table will be merged into a new result set, as shown in the following figure:

Multiple tables connected within Connection, as shown below:

SELECT p.product_name, p.list_price ,c.category_name, b.brand_nameFROM production.products p 
INNER JOIN production.categories c ON c.category_id = p.category_id 
INNER JOIN production.brands b ON b.brand_id = p.brand_id

2. Left connection

Use the LEFT JOIN clause in the left connection to query from multiple Table data, it returns all rows in the left table and matching rows in the right table. If no matching row is found in the right table, use NULL instead to display, as shown in the figure:

Use the LEFT JOIN statement to connect: as shown below:

SELECT p.product_name, p.list_price ,c.category_nameFROM production.products p 
LEFT JOIN production.categories c ON c.category_id = p.category_id

In the above syntax, the table following FROM is the left table (production.products), and the right table (production.categories) immediately following LEFT JOIN.

3. Right join

Use the RIGHT JOIN clause in the right join to query two or more tables. The right query is just the opposite of the left query. It returns all the rows of the right table and the matching rows of the left table. If no matching rows are found in the left table, NULL is returned instead of display, as shown in the figure:

Use the RIGHT JOIN statement to connect: as shown below:

SELECT p.product_name, p.list_price ,c.category_nameFROM production.products p 
RIGHT JOIN production.categories c ON c.category_id = p.category_id

In the above syntax, the table following FROM is the left table (production.products), and the table immediately following RIGHT JOIN Follow the right table (production.categories).

4. Cross join

CROSS JOIN joins two or more unrelated tables. The following is the syntax of CROSS JOIN between two tables. There are two ways of writing:

SELECT T1.select_list 
FROM T1 CROSS JOIN T2;

or

SELECT T1.select_list 
FROM T1, T2;

CROSS JOIN. Combine each row in the first table (T1) with the second table ( Each row in T2) is concatenated. In other words, a cross join returns the Cartesian product of the rows in the two tables. Unlike INNER JOIN or LEFT JOIN, a cross join does not establish a relationship between the joined tables.
Suppose the T1 table contains three rows: 1 , 2 and 3 , and the T2 table contains three rows: A , B and C . CROSS JOIN takes a row from the first table (T1) and creates a new row for each row in the second table (T2). Then it does the same for the next row in the first table (T1), and so on. In this figure, the CROSS JOIN creates a total of 9 rows.

5. Self-join

Self-join is used to connect a table to itself (the same table). It is useful for querying hierarchical data or comparing rows in the same table.
Self joins use inner join or left join clauses. Because queries using self-joins reference the same table, table aliases are used to assign different names to the tables in the query.
Note: If you reference the same table multiple times in a query without using a table alias, an error will occur.
Example:

SELECT t1.select_list 
FROM T1 t1INNER JOIN T1 t2 ON t1.category_id = t2.category_id

You can use other clauses here in INNER JOIN, such as: LEFT JOIN, RIGHT JOIN

6. Full outer join

FULL OUTER JOIN This command returns all rows when there is a match in the left or right table. As shown in the figure:

Use FULL OUTER JOIN to query the data in the pm.projects table and pm.member table:

SELECT m.name member, p.title project 
FROM pm.members m 
FULL OUTER JOIN pm.projects p ON p.id = m.project_id;

pm.projects table data :

pm.member table data:

Query results:

Recommended study: "SQL Tutorial"

The above is the detailed content of Let's talk about SQL table connections. For more information, please follow other related articles on the PHP Chinese website!