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
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. Bid this is to retrieve only matching data.
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
1. A. Union select Column1, column2 from table1 Union select Column1, column2 from Table2 B. Intersection join SELECT * FROM table 1 as a JOIN table2 B on A.name=b.name c. No in SELECT * "from table1 WHERE name does not" (SELECT name from table2) d. Cartesian product The SELECT * FROM table1 CROSS JOIN table2 is the same as the select * from Table1,table2 2. The
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
SQL statement optimization: optimization of JOIN, LEFT JOIN and RIGHT JOIN statements; sqljoin
In database applications, we often need to perform multi-table queries on the database. However, when the data volume is large, multi-table queries have a huge impact on the execution efficiency, therefore, pay special attention to the use of
This section describes table join, more specifically inner joins internal join.Only two records matching each other are selected for the internal join. Therefore, sometimes the records we need are not included.To better understand this concept, we will introduce two tables for demonstration. Party and MSP in the Scottish Parliament ).
Party (Code, name, leader)Code: political partyCodeName: name of a poli
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. field2
The inner
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. field2
The inner
Merge data using relational algebra1 relational algebraThe theoretical basis of Data Set merging is relational algebra, which was proposed by E. F. codd in 1970.In the formal language of relational algebra:? Tables or data sets are used to represent links or entities.? Use rows to represent tuples.? Columns are used to represent attributes.Relational algebra contains the following eight Relational operators? Select to return the rows that meet the specified conditions.? Projection: returns the s
1, outer-join Keywords (many-to-one)
The Outer-join keyword has 3 values, respectively, True,false,auto, and Auto is the default.
True: Uses an outer join to crawl the associated content, which means that when using load (Orderlineitem.class, "id"), Hibernate generates only one SQL statement to initialize the Orderlineitem with his father order.
SELECT * FROM Ord
Chat with a friend today and talk about a small problem in their company. As follows:
Table A equipment table, storage MAC address, province, city, district.Table B software table, store MAC address, software name.The function is to query the list of software by province, city, or district.
What do you think it does now?
It obtains the MAC address through the province, the city, or the district, then inquires the B table to use in the query.It is obviously unreasonable to deal with this many-
Tags: connecting left connection Ali data sheet full join left and right out combination case connections in SQL can be divided into inner joins, outer joins, and cross connections. 1. Cross join Without a WHERE clause, it will return the Cartesian product of the two tables connected, and the number of rows returning the result is equal to the product of two table rows; For example, the following results
Usually we do the association, generally are a table, not too concerned about such a complicated way of writing, then today we look at these writingFor these three kinds of things to say, let's talk about the main points: on the following conditions can be put a few? When is it combined with the Where condition?You can first look at this post, the name of the post is:SQL Server left join in on how to add multiple query conditions??Links: http://bbs.cs
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 district.
Guess how it works now?
It obtains
Reproduced in: http://www.2cto.com/database/201206/137067.html Comparison of SQL Server left Join,right join and inner join today to look at the database left join,Right join and INNER join the difference between the three people
Left join returns records that include all the records in the left table and the equivalent of the junction fields in the right tableRight join returns records that include all records in the right table and the junction fields in the left tableINNER JOIN (equivalent join) returns only rows that have the same
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.