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 on the Internet to read the written source code, the vast majority of application system development, the database will have a lot of various joins, those tables table joins the dazzling, in fact, has always been smattering state, Because in the company does not have to write their own SQL code (I refer to a slightly larger formal software companies, very small companies or to write their own SQL), because the company has a dedicated DBA, usually DBA they write, directly to our stored procedure name let us go to call, (next time, the stored procedure, It's a very important thing, it's almost always used, Just like we do ASP. We must know ado, stored procedures This precompiled SQL script for development is very good, I think in the Wisdom Podcast teacher Yang's basic video only so will be in the page layer code directly write SQL query statements, should be for the convenience of teaching, save time bar) Needless to say, start learning here first give an official explanation:
Left join returns records that include all records in the left table and the equivalent of the junction fields in the right table. The right join returns records that include all records in the right table and the equivalent of the junction fields in the left table inner join (equivalent connection) Returns only two tables in which the junction fields are equal rows are not looking a bit dizzy, so let's try it out for ourselves. The first step: the process of building a table I omitted, because yesterday's test has some simple code, directly on the above to make a slight modification to test, insert a little test data below [SQL] insert INTO Table1 values (1, ' Yiu Yu ') insert into Table1 values (2, ' Benbing ') insert to Table1 values (3, ' Mr. Yuan ') insert into Table1 values (5, ' Preach Wisdom blog ') INSERT into Table2 values (1, ' Yiu Yu ') insert to Table2 values (2, ' Liu Chunping ') insert into Table2 values (3, ' Zhang Yongxu ') insert INTO Table2 VALUES (4, ' Liu Huajian ') [SQL] insert into Table1 values (1, ' Yiu Yu ') insert to Table1 values (2, ' Benbing ') insert INTO Table 1 values (3, ' Mr. Yuan ') insert into Table1 values (5, ' Preach Intelligence blog ') insert into Table2 values (1, ' Yiu Yu ') insert into Table2 values ( 2, ' Liu Chunping ') insert into Table2 values (3, ' Zhang Yongxu ') insert into Table2 values (4, ' Liu Huajian ') to query out the data from the original table as follows:
We compared the two table found that the data in the Table1 are 1, 2, 3, 5, while the data in Table12 are numbered 1, 2, 3, 4, that is, two tables, only the last data is not the same, respectively, TABLE1 in the ID 5 "Preach Wisdom Podcast" The "Liu Huajian" with ID 4 in Table2 is not corresponding. OK, let's go on ...
Step two: Perform left join effect [SQL] SELECT * FROM Table1 a LEFT join Table2 B on a.id = b.id [SQL] SELECT * FROM Table1 A left Join Table2 B on a.id = b.ID
(The number of rows affected is 4 rows) The result shows that the left join is based on the records of a table, a can be regarded as the right table, and B can be regarded as left table. In other words, the records of 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.ID = b.id). The low-record of table B is null.
Step three: Perform right join effect [SQL] SELECT * FROM Table1 a right join Table2 B on a.id = b.id [SQL] SELECT * FROM Table1 A rig HT join Table2 B on a.id = b.ID
(The number of rows affected is also 4 rows) result Description: Look closely, you will find that the result of the left join is exactly the opposite, this time is based on the right table (B), the lack of a table is filled with null. Fourth step: Perform the effect of inner join [SQL] Select * FROM Table1 a INNER join Table2 B on a.id = b.id [SQL] Select * from Table1 A inner joins Table2 B on a.id = b.ID
Result Description:
Obviously, this shows only the records of a.id = b.ID. This shows that inner join is not based on who, it only shows records that match the criteria. Okay, so far, finally figure out the difference between the two, the database is the core and foundation of software development, must learn.
Comparison of SQL Server left Join,right join and inner JOIN
