Label:Prerequisites: Suppose there are two tables, one is the student table and the other is the Student score table. The table data are: One, internal connection-inner jion: The most common connection query may be that of identifying the student's name and score: Select S.name,m.mark from student S,mark m where S.id=m.studentid The above is our most common inner join, that is, within the connection, the student.id=mark.studentid conditions of the
Cross join Is the product of flute Is the number of rows in one table multiplied by the number of rows in the other table.Left join The connection column of the First table does not match in the second table,The value in the second table is returned.NullRight join The join column of the second table does not match in t
1. Inner join(Typical join operations use comparison operators such as = or The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.2. Outer Join.Outer Join can be
In-depth understanding of four SQL connections-left outer join, right outer join, inner join, and full link bitsCN.com
1. INNER JOIN(Typical join operations use comparison operators such as = or The inner join uses the comparison
1. Inner join(Typical join operations use comparison operators such as = or The inner join uses the comparison operator to match rows in two tables based on the values of the columns in each table. For example, retrieve all rows with the same student ID in the students and courses tables.2. Outer Join. Outer
Internal ConnectionINNER Join (equivalent connection):Only rows that are equal to the junction fields in two tables are displayed. This is the same effect as using Select to query multiple tables, so it is seldom used;External connection:Left JOIN: Displays all records in the left table on a left table basis, regardless of whether they match the association criteria, and the data in the right table shows on
The connection conditions can be specified in the FROM or WHERE clause. We recommend that you specify the connection conditions in the FROM clause. The WHERE and HAVING clauses can also contain search conditions to further filter the rows selected by the connection conditions.Connections can be divided into the following types:Internal Connection.(Typical join operations use comparison operators such as = or Equal connection and natural connection.The
SQL left Outer Join, right Outer Join, full join, internal joinThe connection conditions can be specified in the from or where clause. We recommend that you specify the connection conditions in the from clause. The where and having clauses can also contain search conditions to further filter the rows selected by the connection conditions.Connections can be divide
I. Concepts
1. Cross join)Without the WHERE clause, it returns the Cartesian product of the two joined tables, and the number of rows returned is equal to the product of the number of rows in the two tables.For example:A: select a. *, B. * From Table1 A, Table2 B where a. ID = B. IDB: Select * From Table1 a cross join Table2 B where a. ID = B. IDIt is generally not recommended to use methods A and B, becaus
Nested loop join)
Loop nested join is the most basic link. As shown in its name, loop nesting is required. nested loop is the only method that supports inequality connection among the three methods, the process of this connection method can be simply described as follows:
Figure 1. Step 1 of loop nested join
Figure 2. Step 2 of loop nested
Http://www.blogjava.net/zolly/archive/2007/10/23/SQLJION.html
The join condition can be specified in the from or where clause. We recommend that you specify the join condition in the from clause. The where and having clauses can also contain search conditions to further filter the rows selected by the join conditions. Connections can be divided into the followin
--1.left Join left table is the primary table, left table returns all data, and the right table only returns data that matches the left table.Select T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPDM,T2.FPHM,T2.ZFLX from YW_ZJFPJL t1Left join XXDZMX T2 on t2.fpdm| | t2.fphm=t1.fpdm| | T1.fphmSelect T1.fpdm,t1.fphm, T1.ZJR,T1.ZJSJ,T1.ZJJX,T1.ZJJE,T1.ZFLX,T1.ZFID,T2.FPDM,T2.FPHM,T2.ZFLX f
Label:1, Cross join: There are two, explicit and implicit, without an ON clause, returns the product of two tables, also known as the Cartesian product, the number of returned records should be in a and B table in accordance with the record and. Explicit: SELECT [Cols_list] from aCross Joinb where [condition] implicit: SELECT [Cols_list] from a,b where [condition] 2, INNER join (INNER
Label:--Build Table Table1,table2:CREATE TABLE table1 (ID int,name varchar (10))CREATE TABLE table2 (ID int,score int)Insert INTO table1 Select 1,leeInsert INTO table1 Select 2,zhangInsert INTO table1 Select 4,wangInsert INTO table2 Select 1,90Insert INTO table2 Select 2,100Insert INTO table2 select 3,70such as table-------------------------------------------------Table1 | table2 |-------------------------------------------------ID Name |id Score |1 Lee | 90 |2 Zhang 100 |4 Wang |3 70 |---------
Label:Simply and clearly, connect to the inner and outer links. Suppose there are two tables of A and B Internal connection: Inner JOIN indicates that the record of the AB table is displayed, excluding the condition of the AB table . There are three kinds of outer joins, that is, left OUTER joins, right connection, OUTER join, full OUTER join, the following is a
Inner join
There are two tables A and B.
The structure of Table A is as follows:
Aid: int; identifies the seed, primary key, and auto-increment ID
Aname: varchar
The data, that is, the records from select * From A, are shown in 1:
Figure 1: data in Table
Table B has the following structure:
Bid: int; identifies the seed, primary key, and auto-increment ID
Bnameid: int
Data, that is, the records from select * from B, are sh
? The connection conditions can be specified in the from or where clause. We recommend that you specify the connection conditions in the from clause. The where and having clauses can also contain search conditions to further filter the rows selected by the connection conditions.
Connections can be divided into the following types:
Internal Connection.(Typical join operations use comparison operators such as = or Equal connection and natural connectio
Table A records the following:
Aid anum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
Table B records the following:
Bid bname
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
The experiment is as follows:
1. Left join
The SQL statement is as follows:
Select * from
Left join B
On a. Aid = B. Bid
The result is as follows:
Aid anum bid bname
1 a20050111 1 2006032401
2 a20050112
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.