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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.