Key syntax in SQL Union Query

The efficiency of the Combined Query is high. The following example shows the benefits of the combined query.

Table T1 structure (username, password) USERID int username varchar (20) password varchar (20)

1 Jack jackpwd

2 Owen owenpwd

T3 table structure (user points, level) USERID int JF int DJ int

1 20 3

3 50 6

1. inline (inner join)

If you want to list all user information, points, and levels, it is generally written in this way.

Select * from T1, T3 where t1.userid = t3.userid in fact, the result is equivalent to select * from T1 inner join T3 on t1.userid = t3.userid

It is to splice the rows with userid in both tables into one row. This is inline, but the latter is much more efficient than the former. We recommend that you use the latter statement.

Running result: userid Username Password userid JF DJ

1 Jack jacjpwd 1 20 3

Second, left Outer Join shows all rows in the left table.

Select * from T1 left Outer Join T3 on t1.userid = t3.userid

Running result: userid Username Password userid JF DJ

1 Jack jackpwd 1 20 3

2 Owen owenpwd null

Third, right Outer Join shows all rows in the right table.

Select * from T1 right Outer Join T3 on t1.userid = t3.userid

Running result: userid Username Password userid JF DJ

1 Jack jackpwd 1 20 3

Null null 3 50 6

Fourth: Full outer join shows all rows in both tables.

Select * from T1 full outer join T3 on t1.userid = t3.userid

Running result: userid Username Password userid JF DJ

1 Jack jackpwd 1 20 3

2 Owen owenpwd null

Null null 3 50 6

Summary: I have tested the joint query. the efficiency is indeed relatively high. If the four join methods can be used flexibly, the complex statement structure will also be simple. the four methods are as follows:

Inner join left Outer Join right Outer Join full outer join

From: http://www.cnblogs.com/aaapeng/archive/2010/01/20/1652151.html

The efficiency of the Combined Query is high. The following example shows the benefits of the combined query.

Table T1 structure (username, password) USERID int username varchar (20) password varchar (20)

1 Jack jackpwd

2 Owen owenpwd

T3 table structure (user points, level) USERID int JF int DJ int

1 20 3

3 50 6

1. inline (inner join)

If you want to list all user information, points, and levels, it is generally written in this way.

Select * from T1, T3 where t1.userid = t3.userid in fact, the result is equivalent to select * from T1 inner join T3 on t1.userid = t3.userid

It is to splice the rows with userid in both tables into one row. This is inline, but the latter is much more efficient than the former. We recommend that you use the latter statement.

Running result: userid Username Password userid JF DJ

1 Jack jacjpwd 1 20 3

Second, left Outer Join shows all rows in the left table.

Select * from T1 left Outer Join T3 on t1.userid = t3.userid

Running result: userid Username Password userid JF DJ

1 Jack jackpwd 1 20 3

2 Owen owenpwd null

Third, right Outer Join shows all rows in the right table.

Select * from T1 right Outer Join T3 on t1.userid = t3.userid

Running result: userid Username Password userid JF DJ

1 Jack jackpwd 1 20 3

Null null 3 50 6

Fourth: Full outer join shows all rows in both tables.

Select * from T1 full outer join T3 on t1.userid = t3.userid

Running result: userid Username Password userid JF DJ

1 Jack jackpwd 1 20 3

2 Owen owenpwd null

Null null 3 50 6

Summary: I have tested the joint query. the efficiency is indeed relatively high. If the four join methods can be used flexibly, the complex statement structure will also be simple. the four methods are as follows:

Inner join left Outer Join right Outer Join full outer join

From: http://www.cnblogs.com/aaapeng/archive/2010/01/20/1652151.html