SQL Cookbook: Manipulating multiple tables

1. Overlay of record set

Use UNION ALL

Union all contains duplicate results, union filters out duplicates (may need to be sorted)

1 Select *  from where < 5 2 Union  All 3 Select *  from where > 995\g

2, the combination of related lines

1 Select  2 from 3 where = and = and  =ten\g

The above connection method is an equivalent connection, which is one of the inner joins.

If you want the connection logic to be placed in the FROM clause, you can use the Join keyword

1 Select  2fromjoinin=joinon=   3where=ten\g

4, or with a null combination of pits

In SQL, true or null result is true,false or null result is null, for example:

1 mysql>Selectfromwhere not in (1,2 ,3,null) \g2set (0.03 sec)

The reason is that the in query is equivalent to:

 1  mysql>  select  title from  film where  not  (film_id =  1  or  film_id =  Span style= "color: #800000; Font-weight:bold ">2  or  film_id =  Span style= "color: #800000; Font-weight:bold ">3  or  film_id =  Span style= "COLOR: #0000ff" >null  ) \g  2  Empty set  (0.02  sec) 

Where the WHERE clause is equivalent to:

1 or or null)

The final answer is NULL.

To resolve this problem, you can use is to determine null:

1 mysql>Selectfromwherenot=1or  =2or=3orisnull) \g

5. Left Join

A a LEFT outer join b b

Returns all rows in a, match in B returns, or null if no match

6. N-1 Rules

If the FROM clause has n tables, the minimum number of n-1 joins is required to avoid producing a Cartesian product

7. Full OUTER JOIN on

Returns the missing rows from two tables and all matching rows

8. Use null for calculations and comparisons

Use the COALESCE function to convert NULL to a real value that can be used as a comparison of standard values

SQL Cookbook: Manipulating multiple tables