Left join in database query and precautions

I had nothing to do at work, and I was tired of surfing the Internet. So I decided to use self study for a while and I found a database training document for my company.

Turning to join queries, I suddenly remembered a left join problem that I encountered during previous development. So I searched for the old post in csdn and sorted it out.

 
There are three join types during query: inner join, outer join, and cross join.
Inner join uses a comparison operator to compare data in some columns of a table, and lists the data rows in these tables that match the connection conditions. Based on
Internal connections are classified into three types: equivalent connections, natural connections, and unequal connections.
Outer Join is divided into left Outer Join (left Outer Join or left join), right Outer Join (right Outer Join or right join)
Full outer join or full join. Different from the internal connection, the external connection not only lists the matches with the connection conditions
To list all the rows that meet the search criteria in the left table (left Outer Join), right table (right Outer Join), or two tables (full outer join ).
Data rows.
Cross join does not have a where clause. It returns the Cartesian product of all data rows in the join table.
The number of rows equals to the number of rows that meet the query conditions in the first table multiplied by the number of rows that meet the query conditions in the second table.
The ON (join_condition) clause in the Join Operation specifies the join condition, which is composed of columns in the connected table, comparison operators, and logic
Operator.
No connection can be used to directly connect columns of the text, ntext, or image data type, but these columns can be indirectly connected.
Connection. For example:
Select p1.pub _ id, p2.pub _ id, p1.pr _ INFO
From pub_info as P1 inner join pub_info as p2
On datalength (p1.pr _ INFO) = datalength (p2.pr _ INFO)

It seems to be far away. What we want to talk about is left Outer Join (left Outer Join or left join)

When an external connection is used, it returns to the query result set that contains not only rows that meet the connection conditions, but also the left table (left Outer ).
All data rows in the connected table, right table (right Outer Join), or two edge tables (all Outer Join.

The record in the left table must be captured if the where condition is met, regardless of whether the join is successful or not.

　
For example, the current table is like this:

Table

A AA
1 22
2 33
3 44

Table B

A BB CC
1 23 33

There are two types of left join statements: (A is used as the left table)

1) Select a. a B. BB from a, B where a. A * = B.

2) Select a. a B. BB from a left join B on A. A = B.

This is OK. The returned result is 3 Records.

1, 23
2,
3,

The next two BB fields are null.

 
However, if we add a where condition, the results of these two queries will be different.

Select a. a B. BB from a, B where a. A * = B. A and B. Cc = 33
In this case, three records are returned.
1, 23
2,
3,
　　
However
Select a. a B. BB from a left join B on A. A = B. A where B. Cc = 33
But in this way, only
1, 23
 
One record,

Completely dizzy,

So I went online and checked

I finally found the reason.

Select a. a, B. BB from a, B where a. A * = B. A and B. Cc = 33

Equivalent

Select a. a, B. BB from a left join B on A. A = B. A and B. Cc = 33

Equivalent

Select a. a, c. BB from a left join (select * from B where cc = 33) C on A. A = c.

The second type:

Select a. a B. BB from a left join B on A. A = B. A where B. Cc = 33

Equivalent

Select C. AA, C. BB from (select a. a, B. BB, B. CC from a left join B on A. A = B. a) c Where

C. Cc = 33

Finally understand

If there are no other conditions (for example, B. Cc = 33), the results of the two queries are the same,

However, if there are other conditions, such as B. Cc = 33, then if the formula * = is used, this condition is actually equivalent to a join condition,

If left join is used, the condition B. Cc = 33 is equivalent to a query condition, that is, join is performed first, and then based on the results after join.

And then filter the data based on B. Cc = 33.
　　
　
Note: The above query is verified in Sybase and SQL 2000