Data table connection (left connection, right connection, inner connection, Cartesian)

Data table connection (left connection, right connection, inner connection, Cartesian)

--Execution in Query Analyzer: --Build Table Table1,table2: CREATE TABLE table1 (ID int,name varchar (10)) CREATE TABLE table2 (ID int,score int) INSERT INTO table1 Select 1, ' Lee ' INSERT INTO Table1 Select 2, ' Zhang ' INSERT INTO table1 Select 4, ' Wang ' Insert INTO table2 Select 1,90 Insert INTO table2 Select 2,100 Insert INTO table2 select 3,70 such as table ------------------------------------------------- Table1 |table2| ------------------------------------------------- ID name |id score| 1 Lee |1 90| 2 Zhang 100| 4 Wang |3 70| ------------------------------------------------- The following are performed in Query Analyzer One, outer connection 1. Concept: Include left outer joins, right outer joins, or full outer joins 2. Left-side join: A/outer JOIN (1) The result set of the left outer join includes all the rows of the left table specified in the OUTER clause, not just the rows that the join columns match. If a row in the left table does not have a matching row in the right table, all picklist columns in the right table in the associated result set row are null (NULL). (2) SQL statement SELECT * FROM table1 LEFT join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 4wangNULLNULL ------------------------------ Remarks: Contains all the clauses of Table1, returns table2 corresponding fields according to the specified criteria, and does not conform to null display 3. Right connection: Right-hand join or outer join (1) A right outer join is a reverse join of a left outer join. All rows from the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value is returned for left table. (2) SQL statement SELECT * FROM table1 right join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 NULLNULL370 ------------------------------ Remarks: Contains all the clauses of table2, returns table1 corresponding fields according to the specified criteria, and does not conform to null display 4. Complete outer join: Full JOIN or fully outer join (1) A full outer join returns all rows in the left and right tables. When a row does not match rows in another table, the select list column for the other table contains null values. If there is a matching row between the tables, the entire result set row contains the data values for the base table. (2) SQL statement SELECT * FROM table1 full join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 4wangNULLNULL NULLNULL370 ------------------------------ Comments: Returns the and left and right connections (see upper left, right-hand connection) Two, the internal connection 1. Concept: An inner join is a join that compares the values of the columns to be joined with a comparison operator 2. INNER JOIN: Join or INNER JOIN 3.sql statement SELECT * FROM table1 join table2 on Table1.id=table2.id -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang2100 ------------------------------ Remarks: Returns only the Table1 and table2 columns that match the criteria 4. Equivalence (same as the following execution effect) A:select a.*,b.* from table1 a,table2 b where a.id=b.id B:select * FROM table1 Cross join Table2 where table1.id=table2.id (note: Cross join after conditions can only be used where, not on) Cross-connect (complete) 1. Concept: A cross join without a WHERE clause produces a Cartesian product of the table involved in the join. The number of rows in the first table multiplied by the number of rows in the second table equals the size of the Cartesian product result set. (Table1 and table2 cross joins generate 3*3=9 Records) 2. Cross-connect: Cross join (without conditions where ...) 3.sql statement SELECT * FROM table1 cross join Table2 -------------Results------------- Idnameidscore ------------------------------ 1lee190 2zhang190 4wang190 1lee2100 2zhang2100 4wang2100 1lee370 2zhang370 4wang370 ------------------------------ Note: Returns the 3*3=9 record, that is, the Cartesian product 4. Equivalence (same as the following execution effect) A:select * from Table1,table2 -------------------------------------------------------------------------- A LEFT join is typically used to convert a foreign key ID (identity form) value to the actual value of the primary key (name form) Left join, whichever is the left, the table in the right side of a condition left join does not appear on the left-hand side of the data, the data is not in the query results, and the data on the left does not appear on the right, and the query results appear as null values. Multiple LEFT join unless you use parentheses to change the order of the connection, it is always the result of the left-hand query, to the right to find the conditions of the data, found on the use of, can not find the use of NULL