Various joins in MySQL
1. Cartesian Product (cross join)
In MySQL, you can think of cross join or omit cross as join, or use ','
For example
Select * From Table1 cross join Table2
Select * From Table1 join Table2
Select * From Table1, Table2
Because the returned result is
Tags: style blog c http a widthFor SQL joins, learning may be a bit confusing. We know that the join syntax for SQL has a lot of inner, outer, left, and sometimes it's not very clear what the result set looks like for a select. There is an article on Coding horror that explains the join of SQL through the Venn diagrams of the Venturi diagram. I feel clear and und
1. Theory
As long as the public fields of the two tables have matched values, the records in the two tables are combined.
My personal understanding: Use a common field to calculate the intersection of the two tables that meet the requirements, and combine the records that meet the requirements of each table with the common fields.
Syntax
From Table1 inner join Table2 on Table1. field1 compopr Table2
1. Theory
As long as the public fields of the two tables have matched values, the records in the two tables are combined.
My personal understanding: Use a common field to calculate the intersection of the two tables that meet the requirements, and combine the records that meet the requirements of each table with the common fields.
Syntax
From Table1 inner join Table2 on Table1. field1 compopr Table2
1 LEFT JOIN: The query results are based on the left table data. If the left table has four data, the right table has three data, the query result is four, and all the data in the left table.For example:EMP Table:Sal table:Left connection Select * from EMP left join SAL on
EMP. ename = SAL.ename; Left JOIN, the table EMP is the primary table, so the q
table 1 alias a left join Table 2 alias B in alias a. Column 1 equals alias B. Column 1 where alias a. Column 4 = 1 and alias a. Column 5 = 1
7.4. Left join
7.3.1 synchronize data from two tables
Program codeUpdate ctarticle a inner join cttag B ON a. articleid = B. articleid SET B. classid = a. classid, B. nclassid =
In the talk of MySQL before the join grammar or the first review of the coupling of the grammar, oh, in fact, even I have forgotten almost, then we go over it together (if the content has errors or doubt, the domestic information on the MySQL connection is very small, I believe that after reading this article, we will have a fairly clear understanding of the MySQL connection syntax, and will not be confused by the external connection of Oracle ("+").T
table, and a can be viewed as the left-hand table, B can be viewed as the right table, and left table.In other words, the records in the left table (A) will all be represented, and the right table (B) will only display records that match the search criteria (in the example: A.aid = b.bid).b tables where the records are insufficient are null.
2.right join (right join)
The SQL statement is as follows:
Label:First borrow the official explanation under:Inner JOIN (equivalent connection): Returns only rows with the same junction field in two tables;Left join: Returns records that include all records in the left table and the equivalent of the junction fields in the right table;Right join: Returns records that include all records in the right table and the junctio
Label:Left join the left JOIN, That is, the meaning of the left outer join is that the data in the table that restricts the right end of the connection keyword must satisfy the join condition , and the contents of the left-hand table are output if the data in the table in the left side satisfies the
Preface: Do not sum up and forget it!
Let's give a general explanation first.
Example Table
Aid
Adate
1
A1
2
A2
3
A3
Table B
Bid
Bdate
1
B1
2
B2
4
B4
Problem: two tables A and B are connected and fields with the same ID must be retrieved.
Select * from a inner join B on A. Aid = B.
In mysql, the left join, right join, and inner join queries are commonly used. But some friends know the differences between them, next I will introduce you to you.
LEFT JOIN
Two tables in the join relationship are displayed. Afte
Label:INNER JOIN (equivalent join) returns only rows that have the same join field in two tablesLeft join returns records that include all the records in the left table and the equivalent of the junction fields in the right table Right join returns records that include all r
SQL left join, right join, and inner join
Today, I chatted with a friend about a small problem in their company. As follows:
Table A device table stores MAC addresses, provinces, cities, and zones.Table B Software Table, which stores MAC addresses and software names.You can query the Software List by province, city, or
Tags: www. POS nbsp Join table data Intersection case tagThe difference between inner join, outer join, and Cross join in SQL many people do not know, I also ask, just look for information to look at, with their previous understanding, if you use
Left JOIN, Inner join related content, very practical, for understanding the principle and specific applications are very helpful! The LEFT join is a shorthand for the left outer join, and the left join defaults to the outer prope
INNER JOIN ( equivalent connection ) returns only rows in which the junction fields are equal in two tablesLeft join ( left Join ) returns records that include all records in the left table and the equivalent of the junction fields in the right tableRight join ( right
The LEFT join is a shorthand for the left outer join, and the left join defaults to the outer property. Inner JoinThe Inner join logical operator returns each row that satisfies the first (top) input and the second (bottom) input
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.