Shulou(Shulou.com)05/31 Report--

This article shows you what is the use of different connections JOIN and join in SQL sentences. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

In order to get data from two tables, we sometimes join the two tables with JOIN. There are usually several ways to connect:

JOIN or INNER JOIN (inner join): these two are the same, requiring that both tables have corresponding data, return rows, and the missing data on either side will not be displayed.

LEFT JOIN (left outer join): returns all rows from the left table even if there is no match in the right table.

RIGHT JOIN (right outer join): returns all rows from the right table even if there is no match in the left table.

FULL JOIN (full join): returns a row as long as there is a match in one of the tables.

For example, there are grade tables (course number sn, score scroe, student number id) and student form (student number id, student name name), and students' names and grades should be queried.

When JOIN or INNER JOIN

SELECT s.name.g.snrecoverg.score from student as s join grade as g on s.id = g.id

Or

SELECT s.name.g.snrecoverg.score from student as s inner join grade as g on s.id = g.id

The result set is as follows

When LEFT JOIN

SELECT s.name.g.snrecoverg.score from student as s left join grade as g on s.id = g.id

The result set is as follows

When RIGHT JOIN

SELECT s.name.g.snrecoverg.score from student as s right join grade as g on s.id = g.id

The result set is as follows

When FULL JOIN

SELECT s.name.g.snrecoverg.score from student as s full join grade as g on s.id = g.id

The result set is as follows

Note that the two tables are joined with on, and when using left join (right join or full join), the difference between on and where is:

The on condition is the condition used when generating the temporary table. It returns the record in the left table regardless of whether the condition in the on is true or not.

The where condition is the condition that filters the temporary table after the temporary table is generated. At this time, there is no meaning of left join (the record of the table on the left must be returned). If the condition is not true, it will be filtered out. The figure below is as follows

The on condition is yellow.

SELECT s. Name from student as s left join grade as g on s.id g. Sngre g.score from student as s left join grade as g on s.id = g.id and score = 90SELECT s.namedepartment g. Score from student as s left join grade as g on s.id = g.id where score = 90

Let's take a look at the various uses of join in SQL

1. Natural connection (natural join)

The natural join automatically matches the columns with the same name in the table, and the natural join does not have to specify any equivalent join conditions or assume that which columns need to be matched. In the result table obtained by the natural join, columns with the same name in the two tables appear only once.

Select * from employee natural join department

two。 Inner join: the result is the intersection of An and B (the same value in the same column)

The inner join query can join the data that can be associated in the left table and the right table, and the result is all the matching data in the two tables.

Select * from TableA as An inner join TableB B on A.PK = B.PKscape select * from TableA as An inner join TableB B on A.PK > B.PK

3. External connection (outer join)

The inner join is to show the memory of the two tables, while the outer join is not required. The outer join can be divided into left outer join, right outer join and full join according to the behavior of keeping left table, right table or all tables in the join table.

Select * from TableA as A left (right/full) join TableB as B on A.PA = B.PK

Full Join: the result is the union of An and B (null is used as the value if there is no same value)

Left Join: produces a complete set of table A, while a match in table B has a value (no match is replaced by a null value)

Right Join: produces a complete set of table B, while a match in table A has a value (no match is replaced by a null value)

4. Cross connect (cross join)

Also known as Cartesian joins, cross-joins return the Cartesian product of two sets.

Select * from TableA cross join TableB; the above is the use of different connections JOIN and join in the SQL sentence. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.