Connections in SQL (geek time)

Time:2019-9-4

Connections in SQL

One of the core of relational database is connection. In different standards, the connection may be written differently. The two most important SQL standards are SQL92 and SQL99. The following figures indicate the time when the standard was put forward.

Connections in SQL92

The table used in the case is player table, team table and body table. Download:https://github.com/cystanford/sql_nba_data

Cartesian product

Cartesian product is a mathematical operation. Assuming two sets X and Y, the Cartesian product of X and Y is all possible combinations of X and Y.

SQL: SELECT * FROM player, team; //Cartesian product

Simply put, the product of data rows of two tables is the result row.

Equivalent connection

Equivalent join is to connect columns that exist in both tables. It can connect multiple tables equally.

SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id; //equivalent connection

SELECT player_id, a.team_id, player_name, height, team_name FROM player AS a, team AS B WHERE a.team_id = b.team_id; //alias substitution, looks more concise.
Non-Equijoin

When we query multiple tables, if the condition of joining multiple tables is equal sign (if we can use equal sign, there must be the same column), it is equal join, and other operator join is non-equivalent query.

Now to query the height of each player, you can use non-equivalent join query:

SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest and h.height_highest; // Query the height of each player
External connection

External join is a record of querying a party that does not satisfy the conditions. The external join of two tables will have one main table and one from a table. The first table is the main table, and the remaining tables are all from the table. In SQL92 (+) is used to represent the location of the slave table, and in SQL92 there are only left and right outer joins, but not all of them. External connection.

The left outer join is that the table on the left is the main table, and all rows need to be displayed. The table on the right is from the table. The right outer join is the opposite.

SELECT * FROM player, team where player. team_id = team. team_id(+); //left outer connection

SELECT * FROM player, team where player. team_id(+) = team. team_id; // right external connection

However, the author found that (+) was not usable during the test, so please refer to the following SQL99 writing.

SQL: SELECT * FROM player LEFT JOIN team on player. team_id = team. team_id; //left outer connection
SQL: SELECT * FROM player RIGHT JOIN team on player. team_id = team. team_id; //right external connection
Self-connection

Self-join can operate on multiple tables or on the same table. That is to say, the query condition uses the field of the current table (but according to the author’s understanding, it is to connect the query with oneself).

SELECT b.player_name, b.height FROM player as a, player as B WHERE a.player_name ='Blake-Griffin'and a.height < b.height; //Self-connection query player taller than Blake-Griffin

Connections in SQL (geek time)

Connection of SQL99

Or the table above to see how the connection is used in SQL99.

Cross Connection

Cross-connection is Cartesian product in SQL92. CROSS JOIN is used here. The results of Cartesian product can be obtained as follows:

SELECT * FROM player CROSS JOIN team; // Cartesian product using CROSS JOIN

Natural Connection

Natural join is the equivalent join in SQL92. It automatically queries all the same fields in the join table and makes the equivalent join.

SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team; // Using NATURAL JOIN, you can automatically query the same fields in the join table

The disadvantage is that if the design of tables is not standardized, the field names of the two tables may be the same, but the meaning of the expression is different. At this time, the result directly using NATURAL JOIN may not be the desired result. Of course, the advantage is that the writing method is simple.

ON Connection

The ON connection is used to specify the desired connection conditions, which can also help us achieve the function of natural connection.

SELECT player_id, player.team_id, player_name, height, team_name FROM player JOIN team on player.team_id = team.team_id; //ON connection realizes natural connection

On connections can also be non-equivalent connections, as follows

SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON Height BETWEEN h.height_lowest and h.height_highest;//Query player's height level

USING Connection

Equivalent join with name field in USING specified data table

SELECT player_id, team_id, player_name, height, team_name FROM player JOIN team USING(team_id);

Advantages and disadvantages are the same as using NATURAL JOIN

External connection

External connection in SQL99:

  1. Left Outer Connection: LEFT JOIN or LEFT OUTER JOIN
  2. Right External Connection: RIGHT JOIN or RIGHT OUTER JOIN
  3. Full External Connection: FULL JOIN or FULL OUTER JOIN
SELECT * FROM player LEFT JOIN team on player.team_id = team.team_id; //left outer connection
SELECT * FROM player RIGHT JOIN team on player.team_id = team.team_id; //right external connection
SELECT * FROM player FULL JOIN team on player.team_id = team.team_id; //External connection

It is important to note that MySQL does not support outbound joins, otherwise outbound joins will return all rows in the left and right tables. When there are matching rows between tables, the results of the inner join are displayed. When a row does not match in another table, the column selected in another table is shown as null.

That is to say, the result of total external connection = data matched by left and right tables + data not matched by left tables + data not matched by right tables.

Self-connection

Self-connection is described in SQL99 as follows:

SELECT b.player_name, b.height FROM player as a JOIN player as B ON a.player_name ='Blake-Griffin'and a.height < b.height; //self-connection

Connections in SQL (geek time)

It is suggested to use the writing of SQL99 to make it more concise and clear.

Recommended Today

Review of SQL Sever basic command

catalogue preface Installation of virtual machine Commands and operations Basic command syntax Case sensitive SQL keyword and function name Column and Index Names alias Too long to see? Space Database connection Connection of SSMS Connection of command line Database operation establish delete constraint integrity constraint Common constraints NOT NULL UNIQUE PRIMARY KEY FOREIGN KEY DEFAULT […]