Connection queries in SQL

About the application of SQL, must not be inseparable from the query, and relatively complex query, always inseparable from the table connection, a single table operation is not uncommon, but in the application environment most of the queries are for 2, 3 tables or even more table 7, as for the connection, there are internal connections, external links, cross-connection points, Each connection method has its own query keyword to execute. At this time, still remember the concept of these concepts vague, I do not know the so-called, always think that the knowledge of textbooks fantasy abstruse, the concept is obscure, of course, I also often blame the teachers and students casually "copy" textbooks.

One, inner connection (matching data through associative information)

1. Equivalent connection (=, duplicate) 2. No equivalent connection (inequality, size) 3. Natural connection (=, no repetition, through filter conditions)

SELECT *  from a  ,WHERE= b.id  --(ANSI connection syntax, SQL92 standard previous notation)SELECT  *fromINNERJOINon= b.id   --  (SQL92 standard notation)

Second, outer connection

1. Left join, regardless of the match to on condition, the left table data will be fully displayed

2. Right-join no matter if it matches the on condition, the right table data will be displayed completely

3. Fully connected (full join) under certain conditions (column name, number of columns consistent), you can use UNION ALL instead

When there are multiple external links (three outer joins), for example, you need to connect two left joins

Then, a table by 5 data, the first left end result is 5, the second left end after the result is still 5

Three, cross-linking (Cartesian product)

The actual application rarely contact, the query results at first look at sixes and sevens, matching each other, resulting in more duplicate values

SELECT *  from  Cross JOIN

These are just a few concepts, textbooks can be found on the Internet, in fact, when doing these queries, often accompanied by the conditions of filtering, want to write good SQL, the first step is to understand the order of execution of these conditions

SQL order from Join---------------------

As for the more confusing points:

Difference between ①where and on (no difference in inner)

Due to the particularity of left and right (data integrity), where and on are different results for query filtering, because on queries take precedence over where, such as in leave query, where is to filter the data after the query, so that the left table final data is incomplete, and use on, No impact on the completeness of the final left table data

The difference between ②where and having

This is in fact a very good distinction, having the use must accompany the aggregation function group by, where is to filter before grouping, having is to filter after grouping

Connection queries in SQL