1 common table connections (inner join,left join,right join,full Join,cross join)if object_id(N'table1'N'U') is not NULL Drop Tabletable1if object_id(N'table2'N'U') is not NULL Drop Tabletable2Create TableTable1 (IDint, na
statement:SELECT Persons.lastname, Persons.firstname, Orders.ordernofrom Persons on INNER JOIN Orders persons.id_p = Orders.id_porder by Persons.lastnameResult set:
LastName
FirstName
OrderNo
Adams
John
22456
Adams
John
24562
Carter
Thomas
77895
Carter
Thomas
44678
Differ
, you can use keyword join to get data from two tables If you want to list everyone's subscriptions, you can use the following SELECT statement SELECT Persons.lastname, Persons.firstname, Orders.orderno from
Persons
INNER JOIN Orders on
persons.id_p = Orders.id_p
ORDER by Persons.lastname
Result set:
LastName
FirstName
OrderNo
Tags: suggest a code empty ONS ... select null tarIn general, the use/difference of four joins can be described as:
The LEFT join returns all records from the table (shop), even if there are no matching rows in the right table (Sale_detail).
Right outer join, returns all records in the right table, even if no record matches it in the left table
Full outer
Adate1 A12 A23 A3Table BBid Bdate1 B12 B24 B4Two tables A, B connected, to remove fields with the same IDSELECT * from a INNER join B on a.aid = B.bid This is only the matching data is taken out.At this point, the removal is:1 A1 B12 A2 B2Then the left join means:SELECT * from a LEFT join B on a.aid = B.bidFirst remov
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
null. :mysql> SELECT g.goods_name,g.cat_id as g_cat_id, c.cat_id as c_cat_id,c.cat_id from Mingoods G left JOIN mincategory C on g.cat_id = c.cat_id; 3. Right join query ... on ...Syntax: Select A.field1,a.field2,..., b.field3,b.field4 from join
on The right connection query is similar to the left JOIN query,
9.3.3 INNER JOIN (INNER join)
The inner join is also called an equivalent connection, and the result set returned is all the matching data in the two tables, and the mismatched data is discarded. That is, in such a query, the DBMS
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
join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.Right join or right outer join.The right outer connection is the reverse connection of the left outer connection. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null va
list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.(2) SQL statementsSELECT * FROM table1 full join table2 on Table1.id=table2.id-------------Results-------------ID Name ID Score------------------------------1 Lee 1 902 Zhang 2 1004 Wang Null NULLNULL NULL 3 70------------------------------Note: Returns the left and right connected
2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
Null null 8 2006032408
(The number of affected rows is 5)
Result description:
After careful observation, we will find that the result of left join is exactly the opposite. This time, it is based on the right table (B) and is filled with null when table A is insufficient.
3. Inner join
The SQL stateme
Reprinted from: http://www.cnblogs.com/still-windows7/archive/2012/10/22/2734613.htmlPrerequisites: 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.studentidThe above is our most common
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
all expressed, while the right table (B) only displays records that meet the search criteria (in this example:. aid = B. bid ).All records in Table B are null.--------------------------------------------2. Right joinThe SQL statement is as follows:
select * from A right join B on A.aID = B.bID
The result is as follows:Aid anum bid bname1 a20050111 1 20060324012 a20050112 2 20060324023 a20050113 3 20060324034 a20050114 4 2006032404Null null 8 20060324
Test table:EMP Table Sal TableLeft JOIN: Results All rows are displayed in the table, and the right table determines that the column is the same as the left.SELECT * FROM EMP left join SAL on EMP. ename = SAL. ename; Right join: Results All rows are displayed in the table on the left, the table on the right is consistent with the tableSelect * from EMP right
Label:Transferred from: http://blog.csdn.net/jz20110918/article/details/41806611 Let's say we have two tables. Table A is the sheet on the left. Table B is the list on the right. Each of them has four records, of which two records name is the same, as follows: Let's look at the different joins
Table A
Id
Name
1
Pirate
2
Monkey
3
Ninja
4
Spaghetti
Let's start by looking at the results of the left join and the right join and the Inner join and the full join working on the table.
Create a new two table in the database and insert the data you want to test.
New table:[SQL]View PlainCopyUse [Test]GO/****** ob
Tags: http strong ar Data div sp on ad efLet's start by looking at the results of the left join and the right join and the Inner join and the full join working on the table.
Create a new two table in the database and insert the data you want to test.
New table:[SQL]Use
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.