Test methods for differences between SQL server Join and SQL server join

Test methods for differences between SQL server Join and SQL server join

This section describes the differences among Inner Join, Full Out Join, Cross Join, Left Join, and Right Join.

Inner Join: filter records on both sides
Full Out Join: both sides are filtered Out, matching can be matched, and non-matching can be listed using NULL.
Cross Join: lists all the combinations on both sides, also known as Cartesian set A × B
Left Join: The table on the Left is used as the primary table. It lists all records in the primary table. If matching matches, NULL is used to list records that cannot match.
Right Join: uses the table on the Right as the main table to list all records in the main table. If the table matches, use NULL to list the records that do not match.

Let's look at the code below:

Create a test table:

CREATETABLE CONSUMERS (CONSUMER_ID INTNOTNULL,CONSUMER_NAME VARCHAR(10) NOTNULL)CREATETABLE ORDERS(CONSUMER_ID INTNOTNULL, ORDER_ID VARCHAR(10) NOTNULL)

Compile Test Data

INSERT CONSUMERS VALUES ( 1, 'AA')INSERT CONSUMERS values ( 2, 'BB')INSERT CONSUMERS values ( 3, 'CC')INSERT ORDERS VALUES ( 2, 'O100001')INSERT ORDERS VALUES ( 3, 'O100002')INSERT ORDERS VALUES ( 3, 'O100003')INSERT ORDERS VALUES ( 4, 'O100004')

Test

-- Inner Join -- filter records on both sides SELECT * from orders o inner join consumers cON o. CONSUMER_ID = c. CONSUMER_ID -- Full Out Join -- both sides are filtered Out, matching can be matched. If not, use NULL to list SELECT * from orders o full outer join consumers cON o. CONSUMER_ID = c. CONSUMER_ID -- Cross Join -- lists all the combinations on both sides, that is, Cartesian set A × BSELECT * from orders o cross join consumers c -- Left Join -- uses the table on the Left as the primary table, list all records in the primary table. If the matching conditions are met, use NULL to list SELECT * from consumers c left join ORDERS oon c. CONSUMER_ID = o. CONSUMER_ID -- Right Join -- use the table on the RIGHT as the primary table to list all records in the primary table. If the matching matches, use NULL to list SELECT * from consumers c Right join orders oon c. CONSUMER_ID = o. CONSUMER_ID

OK. For details, refer to the previous articles published by the customer's house.