Do you really play with SQL? Internal connection, outer connection

Can you really play with SQL? Internal connection, outer connection

Most people generally write multi-table queries that will write a select * from TbA, TbB does not use the JOIN keyword, too low, the official website standard recommendation is to use join to clear the relationship between the table, the following specifically. Connection Type:

1. Cross join to get all combinations of connected tables ( Cartesian sets ) crosses join
2. An inner join gets the record combination of the connection table satisfying conditions inner join on
3. outer Joins (left and right) get all the rows of a table, and the rest of the table satisfies the join condition line Full | Left | Right outer join on

Cross Join

Within the result set of such joins, there is one row for each of the two possible pairs of rows in two tables.

However, if you add a WHERE clause to a cross join, it is equivalent to an inner join

Cases:

SELECT title, pub_name         from  Cross JOIN Publishers        Where = publishers.pub_id

This is equivalent to the beginning of our writing: SELECT title, pub_name from titles, publishers Where titles.pub_id = publishers.pub_id

Execution process:

Inner joins

Only joins that match rows from two joined tables are displayed. (This is the default join type in the Query Designer.) ）

Cases:

SELECT title, pub_name         from INNER JOIN Publishers         on = publishers.pub_id

Execution process:

left Outward join

Includes all rows from the first named table (the "left" table, which appears on the leftmost side of the JOIN clause). Does not include unmatched rows in the right table.

Cases:

SELECT titles.title_id,titles.title,publishers.pub_name          from  Left OUTER JOIN Publishers          on = publishers.pub_id

Right Outward join

Includes all rows in the second named table (the "right" table, which appears at the far right of the JOIN clause). Does not include unmatched rows in the left table.

Cases:

SELECT titles.title_id, Titles.title,publishers.pub_name    from  Right OUTER JOIN Publishers          on = publishers.pub_id

Execution process:

full outer joins

Include all rows from all joined tables, regardless of whether they match.

Cases:

SELECT titles.title_id, Titles.title,  publishers.pub_name        from fullOUTER JOIN Publishers          on = publishers.pub_id

Practice:

The use case database used since then is SQL2008.

Use case Database file: Link:http://pan.baidu.com/s/1qW1QxA0 Password: dqxx

/*Returns the value of the customer who placed orders under February 12, 2007, as well as their orders. It also returns customers who have not placed an order on February 12, 2007. Related tables: sales.customers tables and Sales.orders tables. Expected output (shown in abbreviated format):*/custid CompanyName OrderID OrderDate----------- --------------- ----------- ----------------------- theCustomer AhpopNULL        NULL -Customer AhxhtNULL        NULL -Customer AzjedNULL        NULL -Customer BsvarNULL        NULL theCustomer CcfizNULL        NULL... -Customer FVXPQNULL        NULL -Customer GCJSGNULL        NULL theCustomer GllagNULL        NULL -Customer GybbyNULL        NULL4Customer HFBZGNULL        NULL5Customer Hgvlz10444        -- Geneva- A xx:xx:00.000 theCustomer IaijkNULL        NULL theCustomer IBVRGNULL        NULL theCustomer IRRVLNULL        NULL theCustomer jmikwNULL        NULL theCustomer juwxkNULL        NULL... +Customer KIDPXNULL        NULL -Customer KSLQFNULL        NULL -Customer KzqztNULL        NULL inCustomer LcoujNULL        NULL theCustomer LcybzNULL        NULL theCustomer lhant10443        -- Geneva- A xx:xx:00.000 -Customer LjucaNULL        NULL -Customer LoljoNULL        NULL $Customer LVJSONULL        NULL -Customer LWGMDNULL        NULL inCustomer MdlwaNULL        NULL...

Reference sql:

--Answer:Selectc.custid,c.companyname,o.orderid,o.orderdate fromSales.customers asC Left JoinSales.orders aso onC.custid=O.custid andO.orderdate='2007-2-12'/*1. Set the table Sales.customers alias to C and the table Sales.orders alias to o apply on filter to CustID and o.orderdate= ' 2007-2-12 ' as conditional left OUTER join, generate virtual table vt1,2. Add an external row, An attribute in a non-reserved table in an outer row is assigned a value of NULL to generate a virtual table VT23. Processes the select list and finds C.custid,c.companyname,o.orderid,o.orderdate from the virtual table VT2 to generate the virtual table VT3*/Note andO.orderdate='2007-2-12'Change intowhereO.orderdate='2007-2-12', please note that this result is what?

View Code

/* returns customers who have not placed an order. Tables involved: sales.customers tables and Sales.orders tables. Expected output:*/custid      CompanyName--------------------------22            Customer dtdmn          customer Wvaxs

Reference sql:

--Answer:SelectC.custid,c.companyname fromSales.customers asC Left JoinSales.orders aso onC.custid=O.custidwhereO.orderid is NULL/*1. Set the table Sales.customers alias to C and the table Sales.orders alias to o apply the on filter to CustID as the conditional left outer JOIN, generating the virtual table vt1,2. Adds an outer row, an attribute in a non-reserved table in an outer row is assigned a value of NULL, and a virtual table VT23 is generated. Apply where filter to select O.orderid the null data to generate the virtual table VT34. Process the select list to find out C.custid,c.companyname generate the virtual table VT4*/

View Code

Do you really play with SQL? Internal connection, outer connection