Usage of connectors in sql

Connectors in SQL are used to join tables, rows or values ​​to combine data, including INNER JOIN (matching rows), LEFT JOIN (returning all rows of the left table), RIGHT JOIN (returning all rows of the right table) rows), FULL JOIN (returns all rows), UNION (merges tables), UNION ALL (retains all rows), INTERSECT (returns intersection), EXCEPT (returns rows in the left table that are not included in the right table), operations symbols (string concatenation, multiplication, division) and || (string concatenation).

Connector in SQL

In SQL, the connector is used to connect two or more tables, row or value. Its main role is to combine data to create more complex and comprehensive data sets.

Table Join

• INNER JOIN: Join two tables with rows that match columns, returning only matching rows.
• LEFT JOIN: Join two tables and return all rows from the left table, and the right table data for rows matching the right table (if it exists).
• RIGHT JOIN: Opposite of LEFT JOIN, returns all rows from the right table, and left table data for rows that match the left table, if present.
• FULL JOIN: Returns all rows from both tables, as well as matching rows from each table.

Row join

• UNION:Merge two or more tables or query results with the same structure, move Remove duplicate rows.
• UNION ALL: Merge two or more tables or query results with the same structure, retaining all rows, including duplicate rows.
• INTERSECT: Returns intersecting rows from two or more tables or query results.
• EXCEPT: Returns the rows in the first table or query result that are not included in the second table or query result.

Value concatenation

• : Connection string.
• -:Subtraction operation.
• *: Multiplication operation.
• /： Division operation.
• ||: Connection string (in ANSI SQL standard).

Example

Suppose we have two tables:

<code>学生表：
| id | 姓名 | 年龄 |
|---|---|---|
| 1 | John | 20 |
| 2 | Mary | 21 |

课程表：
| id | 课程名称 | 学生id |
|---|---|---|
| 1 | 数学 | 1 |
| 2 | 科学 | 2 |</code>

To return information about students registered for courses, we can use LEFT JOIN:

<code class="sql">SELECT *
FROM 学生表
LEFT JOIN 课程表
ON 学生表.id = 课程表.学生id;</code>

This will output:

<code>| id | 姓名 | 年龄 | id | 课程名称 | 学生id |
|---|---|---|---|---|---|
| 1 | John | 20 | 1 | 数学 | 1 |
| 2 | Mary | 21 | 2 | 科学 | 2 |</code>

The above is the detailed content of Usage of connectors in sql. For more information, please follow other related articles on the PHP Chinese website!