Join multi-table for database SQL

Excerpt article

SQL join is used to query data from these tables based on the relationship between the columns in two or more tables. Note that the number of data records after join is not necessarily a simple connection to the left or right table, the chart represents only the collection relationship, is not accurate in quantity, as a result of this condition, the number is greater than the total number of left tables.

Diagram SQL Join

I think Ligaya Turmelle's article on SQL Join is a great beginner's guide to Getting started. Since SQL Join seems to be based on the default, and the use of a venturi chart to explain it, at first glance it seems to be a natural choice. However, as she wrote in the comments below, I also found that in the actual test, the Venturi diagram does not fully conform to the SQL Join syntax.

I like the idea, but let's start by looking at whether it will work in practice. Let's say we have the following two tables, table A on the left and table B on the right, we'll populate each of the four records.

id name       id  name-- ----       --  ----1  Pirate     1   Rutabaga2 Monkey 2 Pirate3 Ninja 3 Darth Vader4 Spaghetti 4 Ninja

Let's connect these tables in several different ways through the name field to see if we get a conceptual match for those pretty Venn diagrams.

SELECT * FROM TableAINNER JOIN TableBON TableA.name = TableB.nameid name id name-- ---- -- ----1 Pirate 2 Pirate3 Ninja 4 Ninja

Inner joinThe resulting set of results is the intersection of a and B.

select * from tableaFULL outer join tablebON TableA.name = Tableb.nameid name ID name------------1 Pirate 2 pirate2 Monkey null null3 Ninja 4 Ninja4 spaghetti null null null null 1 Rutabaganull null 3 Darth Vader     

Full outer joinProduces A and B's set. It is important to note, however, that for records that do not have a match, NULL is the value.

SELECT * FROM TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameid name id name-- ---- -- ----1 Pirate 2 Pirate2 Monkey null null3 Ninja 4 Ninja4 Spaghetti null null

Left outer joinThe full set of table A is generated, and the match in table B has a value, and no match is substituted with a null value.

SELECT * FROM TableALEFT OUTER JOIN TableBON TableA.name = TableB.nameWHERE TableB.id IS nullid name id name-- ---- -- ----2 Monkey null null4 Spaghetti null null

To produce a set of records only in Table A, instead of in table B, we do the same Left outer join , and then we exclude the right-hand record by a WHERE clause that we do not want.

SELECT *From TableAfull outer JOIN TableBon tablea.name = Tableb.namewhere tablea.id  is nullor tableb.id IS nullid name ID name------------2 Monkey null null4 Spaghetti null nullnull  Null 1 rutabaganull null 3 Darth Vader            

To generate the recordset unique Table A and table B, we do the same full outer join , and then pass a WHERE clause that excludes the records we want from both sides.

Also need to register is we also have a "collection of" cross join , this join no way to use Wenshitu, because it is the table A and table B of the data of a n*m combination, that is, Cartesian product. The expression is as follows:

SELECT * FROM TableACROSS JOIN TableB

This Cartesian product produces 4 x 4 = 16 records, which, in general, are seldom used in this syntax. But we have to be careful, if you do not use nested SELECT statements, the general system will produce a Cartesian product and then filter. This is very dangerous for performance, especially when the table is very large.

Original A Visual explanation of SQL Joins
Translation Segmentfault
Reference Cool Shell

SQL (on in join with where difference)

by Zhang Ying • August 3, 2012

Left join: Connects all the records in the left table with the same records as the join fields in the right table.

Right join: Connects all the records in the right table with the same records as the join fields in the left table.

INNER JOIN: An inner join, also known as an equivalent connection, returns only rows in which the joined fields are equal in two tables.

Full join: Outer JOIN, returns rows from two tables: Left JOIN + RIGHT join.

Cross join: The result is a Cartesian product, that is, the number of rows in the first table multiplied by the number of rows in the second table.

Keyword: on

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using left Jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

Suppose there are two tables:

Table 1:TAB2

Id	Size
1	10
2	20
3	30

Table 2:TAB2

Size	Name
10	Aaa
20	Bbb
20	Ccc

Two sql:
1. Select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size) where tab2.name= ' AAA '
2. Select * Form TAB1 LEFT join tab2 on (tab1.size = tab2.size and Tab2.name= ' AAA ')

The first SQL procedure: 1. Intermediate table On condition: Tab1.size = Tab2.size (Note: There are 4 records, the total number of records is greater than TAB1) Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa 2 20 20 Bbb 2 20 20 Ccc 3 30 (NULL) (NULL) 2, then the intermediate table filter Where Condition: Tab2.name= ' AAA ' Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa

The second SQL procedure: 1, intermediate table on condition: Tab1.size = tab2.size and tab2.name= ' AAA ' (the condition is not true also returns records from the left table) Tab1.id Tab1.size Tab2.size Tab2.name 1 10 10 Aaa 2 20 (NULL) (NULL) 3 30 (NULL) (NULL)

In fact, the key reason for the above results is the particularity of the left Join,right Join,full join, regardless of whether the on condition is true will return the records in the left or right table, and full has a set of attributes of left and right. and inner jion does not have this particularity, the condition is placed in and where, the result set returned is the same.

Join multi-table for database SQL