Several connections for SQL: internal, leftist, right, full, cross-connect

Source: Internet
Author: User
Tags joins

SQL connections can be divided into internal, external, and cross-joins.

Database data:

Book Table Stu Table

1. Internal connection

1.1. Equivalent connection: Use the equals sign (=) operator in the join condition to compare the column values of the connected column, and its query results list all the columns in the joined table, including the repeating columns.

1.2. No equivalent connection: Use a comparison operator other than the equals operator in the join condition to compare the column values of the connected columns. These operators include >, >=, <=, <,!>,!<, and <>.

1.3. Natural connection: Use the Equals (=) operator in the join condition to compare the column values of the connected column, but it uses the selection list to indicate which columns are included in the query result collection and to delete the duplicate columns in the Join table.

INNER JOIN: The INNER JOIN query operation lists the data rows that match the join criteria, which uses comparison operators to compare the column values of the connected columns.

SELECT * FROM book as a,stu as B where A.sutid = B.stuidselect * FROM book as a inner joins Stu as B on a.sutid = B.stuid

The inner connection can be used in both ways, where the inner of the second method can be omitted.

The connection results are as follows A.stuid = B.stuid.

2. External connection

2.1. Left join: is based on left table, A.stuid = B.stuid data is connected, and then the left table does not have a corresponding item, the right table column is null

SELECT * from book as a LEFT join Stu as B on a.sutid = B.stuid

2.2. Right connection: the right table is the benchmark, the A.STUID = B.stuid data is connected, then the right table does not have a corresponding item, the left table column is null

SELECT * FROM book as a right joins Stu as B on a.sutid = B.stuid

2.3. Full connection: A full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

SELECT * from book as a full outer joins Stu as B on a.sutid = B.stuid

3. Cross-Connect

Cross join: Cross joins return all rows from the left table, and each row in the left table is combined with all rows in the right table. Cross joins are also called Cartesian product.

SELECT * from book as a cross join Stu as B order by a.id

Several connections for SQL: internal, leftist, right, full, cross-connect

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.