SQL Get started Classic (iii) connection query

The previous article describes the query. This article is mainly about connection queries, which will introduce inner Join,outer join (left and right), full Join,cross join.

Connecting Gunas means merging multiple data table data into a single result set.

Retrieving matching data with an internal connection (inner join)

The connection structure syntax is as follows: SELECT <column list> from <first table> <join_type> <last table> [on <join condition& gt;]

Then the above. The manager has a new need. Ask you to check the order date for each item, and look at the following 3 requirements code:

Select P.productid,p.name,o.unitprice,o.orderqty,o.modifieddate from Production.Product as P inner join Sales.SalesOrderDetail as O on P.productid=o.productid--query to 121,317 data sets select P.*,o.unitprice,o.orderqty,o. ModifiedDate from Production.Product as P inner joins Sales.SalesOrderDetail as O on P.productid=o.productid  ----Query to 12 1317 Datasets (note that the following p.* are different.) * represents all columns. P means all columns of p are not queried. Select Productid,o.unitprice,o.orderqty,o.modifieddate from Production.Product as P inner join Sales.SalesOrderDetail as O on P.productid=o.productid

--(Unable to query to, run a problem: returned "MSG 209, Level 16, State 1, 1th row name ' ProductID ' ambiguous. "error message because SQL does not know that ProductID is the column of that table)

Read 2 data sheet data (mainly on external connections and full connections)

Order

Id	Name	Contact
1	Test3	Null
2	Test4	1
3	Test5	5
4	Test 6	2

Contact

Id	Name
1	Test1
2	Test2
3	Test3
4	Test4

Retrieve more data using external (outer join).

External connections are rarely used for actual development. We also need to understand external connections.

The basic syntax for external connections is similar to internal connections: SELECT <select column> from <the table, want to being the left table> <left| Right>[outer] JOIN <table you want to being the right table >[on] <join condition> OUTER can be shortened. In order to follow the ISO standardized code, I added

SELECT o.*,c.* from  Order as O inner join contact as C on c.id=o.contact--inner query only 2 results. SELECT o.*,c.*  from Order as o the left join contact as C on c.id=o.contact--left query only 4 results, 2 results c.id and c.name are null, data does not match to the to null. SELECT o.*,c.* from  Order as O right join contact as C on c.id=o.contact--right query only 4 results with 2 results O. The ID and o.name,o.contact are null, and the data does not match to null.

External connections primarily retrieve more orphaned data

Fully connected (full join)

External connections are rarely used, as long as you understand the concept. As the name implies, the full connection is the left and right union queries. Returns all data for 2 tables. No match to value is null instead. Look at the external connection SQL statement first

SELECT o.*,c.* from Order as O full JOIN contact as C on c.id=o.contact--a total of 6 data (supposedly, it should be 4+4=8 data, because there are 2 data in the middle to match, all 6)

Cross Join

A cross connection is also called a Cartesian connection. This is used less and is used primarily to test large amounts of data. Make an analogy. There are 2 of tables. One is to save a last name with 100 last names, and a saved name with 1000 names. I want 100,000 names. The cross connection is used. Continue to the table above.

SELECT o.*,c.* from Order as O cross JOIN contact as C  -generates 16 data. The amount of data is a bit too large to be posted.

Early syntax in SQL2000. Although it is not in use now. Now the sql2012 version is not compatible. Remember early grammar when you meet.

Internal connection: SELECT * from Order,contact where order.contact=contact.id; instead of inner join and where instead of on.

External connection: SELECT * from Order,contact where order.contact*=contact.id; replace (left|right) join and where instead of on, back condition (*= left connection, =* right connection)

Cross-connect: SELECT *from order,contact; remove crosses JOIN to add a keyword

Union (Union)

Then the above table: relatively simple, that is, 2 table data merged into one table (note that 2 tables of data structures and columns to union)

Select Id,name form contactunion (All) Select Id,name form Order--all is an optional parameter. If no all,2 result set has coincident data, discard one, plus all is not discarded.

SQL Get started Classic (iii) connection query