SQL Server difference test method for several joins _mssql

This is a major introduction to the difference between the next inner join, the full out join, the Cross join, the left join, and the right join.

Inner Join: Filter records on both sides
Full out Join: Both sides are filtered out, matching can match, cannot match the list with null
Cross Join: Lists all the combinations on both sides, also known as Cartesian sets AXB
Left Join: Lists all records in the main table, matches, and cannot match, with null lists as the table on the left-hand side
Right Join: Lists all records in the primary table, matching matches, mismatched null lists, with the table on the right-hand side as the main table

Here's a look at the code:

To create a test table:

CreateTable Consumers 
(
consumer_id intnotnull, consumer_name VARCHAR
)

CreateTable ORDERS
(
consumer_id intnotnull, 
order_id VARCHAR)

Compiling test data

Insert consumers values (1, ' AA ')
Insert consumers values (2, ' BB ')
Insert consumers values (3, ' CC ')

in SERT Orders VALUES (2, ' O100001 ')
Insert Orders VALUES (3, ' O100002 ')
Insert Orders VALUES (3, ' O100003 ') 
   insert ORDERS VALUES (4, ' O100004 ')

Test

--inner Join
--filter on both sides of the record
SELECT * from
ORDERS o Inner Join consumers c on
o.consumer_id = c.consumer_id
   --full out Join
--both are filtered out, matched to match, and cannot be matched with null list
SELECT * from
ORDERS o full OUTER Join consumers C on
O. consumer_id = c.consumer_id

--cross Join
--Lists all the combinations on both sides, that is, the Cartesian set AXB
SELECT * from
ORDERS o Cross Join Consumers C

--left Join
--Take the table on the left as the main table, list all the records in the primary table, match the matches, and not match the null list
SELECT * from 
consumers Join ORDERS o on
c. consumer_id = O. consumer_id

--right Join
--Lists all records in the primary table as the table on the right, matches the matching, does not match, and lists the
SELECT * from 
consumers C with null Right JOIN ORDERS o on
c. consumer_id = O. consumer_id

OK, the specific people can refer to the cloud Habitat community previously published articles.