SQL table Connection Diagram _ database other

Source: Internet
Author: User
Tags joins one table

You can look through the picture

Multi-table query is divided into inner and outer joins

An outer join is divided into a left join (a left-hand join or a outer join), a right join (a right-click join, or a right-hand outer join), and a full outer join (either a complete join or a fully outer join)

The result of a LEFT join (left JOIN, or outer join) is all of the rows of the left-hand table in the RIGHT join clause, rather than just the rows that the linked columns match, if a row in the left table does not match in the right table, all selected columns in the right table in the associated result row are null (NULL)

SQL Syntax select * FROM table1 LEFT join table2 on table1. Condition column name = table2. Condition column name;

Note: All columns and matching columns in Table1 are displayed

Right-join outer JOIN, which doesn't do long here. This left connection is very much like but the opposite, just say the syntax

Select *from table1 Right join table2 on table1. Condition column = table2. Condition column

Full outer joins (fully join or outer join)

Displays all the rows in the left and right tables, and when there are no matching rows in one table, the select list column for the other table contains null values (NULL) and displays all data if one is present

SQL syntax:

Select *from table1 full join table2 on table1. Condition column name = table2. Condition Column Name

Internal connections:
concept: An inner join is a connection that compares the values of a connection column with a comparison operator

Inner joins (join or inner join)

SQL syntax:

Select *fron table1 join table2 on table1. Condition column name = table2. Condition Column Name

Returns two table columns that match the criteria

Equivalent to:

Select a *, b* from table1 A, table2 B where a. Condition column name =b. Condition Column Name
Select *form table1 Cross join table2 where table1. Condition column name = table2. Condition column Name (note: The Cross join cannot be followed on only where)

Cross Connection (FULL)

Concept: No cross connection with a WHERE clause multiplies the number of rows in the first table of the Cartesian product that is involved in the connection, multiplied by the number of rows in the second table equals the size of the Cartesian product and result set

Cross-connect: Cross join (without a condition where, if a matching number of rows is returned or displayed)

SQL syntax:

Select *from table1 Cross join Table2

If there is a condition (where)

SELECT * FROM table1 cross join Table2 where table1. Condition column name = table2. Condition Column Name

Equivalent to

Select *from table1,table2 (without where)

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.