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:
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 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.
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 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-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
Connection of SQL99
Or the table above to see how the connection is used in SQL99.
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 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.
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
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 in SQL99:
- Left Outer Connection: LEFT JOIN or LEFT OUTER JOIN
- Right External Connection: RIGHT JOIN or RIGHT OUTER JOIN
- 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 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
It is suggested to use the writing of SQL99 to make it more concise and clear.