Key syntax in SQL Federated queries

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

Joint queries are highly efficient. The following examples illustrate the benefits of federated queries

T1 table structure (user name, password) userid int username varchar password varchar (20)

1 Jack Jackpwd

2 Owen Owenpwd

T3 table structure (user integral, rank) userid int JF int DJ INT

                                                  1                      20                        3

3 50 6

First: inline (inner join)

If you want to list the user information, points, ratings. That's what it usually says.

SELECT * from T1, t3 where T1.userid = T3.userid In fact such results are equivalent to select * from t1 inner join T3 on T1.userid=t3.userid

is to line up a row with the UserID in all two tables. This is inline. But the latter is much more efficient than the former. The latter is recommended.

Run Result: UserID username password userid JF DJ

1 Jack Jacjpwd 1 20 3

Second: Leftist (left outer join) shows all rows in the table

SELECT * from T1 left outer joins T3 on T1.userid=t3.userid

Run Result: UserID username password userid JF DJ

1 Jack Jackpwd 1 20 3

2 Owen OWENPWD null NULL NULL

Third: Right-outer JOIN displays all rows in the right table

SELECT * FROM T1 right outer join T3 on T1.userid=t3.userid

Run Result: UserID username password userid JF DJ

1 Jack Jackpwd 1 20 3

NULL NULL NULL 3 50 6

IV: FULL OUTER JOIN displays all rows in both tables

SELECT * FROM T1 full outer join T3 on T1.userid=t3.userid

Run Result: UserID username password userid JF DJ

1 Jack Jackpwd 1 20 3

2 Owen OWENPWD null NULL NULL

NULL NULL NULL 3 50 6

Summary, on the joint inquiry, I have already tested. The efficiency is indeed relatively high, 4 kinds of Union way if can use flexibly, basically complex sentence structure also will be simple. These 4 ways are:

Inner join outer JOIN right OUTER join full OUTER JOIN

Key syntax in SQL Federated queries