"Table A"
Aid Aname Acode
1 AA 001
2 BB 002
3 CC 003
4 DD 004
5 EE 005"Table B"
Bid bname Bcode
1 AAA 101
3 BBB 102
5 CCC 103
7 DDD 104
9 eee----------------------------------------------------------------------------
Left join: The LEFT join returns records that include all records in the left table and the equivalent of the junction fields in the right table. Cases:
SELECT *
From A
Left JOIN B
On A.aid=b.bid
Results:
Aid Aname acode Bid bname bcode
1 AA 001 1 AAA 101
2 BB 002 null NULL NULL
3 CC 003 3 BBB 102
4 DD 004 NULL NULL NULL
5 EE 005 5 CCC 103----------------------------------------------------------------------------Right join: Right Join
Returns records that include all the records in the right table and the equivalent of the junction fields in the left table. Cases:
SELECT *
From A
Right JOIN B
On A.aid=b.bid
Results:
Aid Aname acode Bid bname bcode
1 AA 001 1 AAA 101
3 CC 003 3 BBB 102
5 EE 005 5 CCC 103
null NULL NULL 7 DDD 104
null NULL NULL 9 eee----------------------------------------------------------------------------INNER JOIN: INNER JOIN
Only rows that are equal to the junction fields in two tables are returned.
Cases:
SELECT *
From A
INNER JOIN B//inner can not write
On A.aid=b.bid
Results:
Aid Aname acode Bid bname bcode
1 AA 001 1 AAA 101
3 CC 003 3 BBB 102
5 EE 005 5 CCC 103----------------------------------------------------------------------------Full Join: Full Join
Returns all the fields in two tables.
Cases:
SELECT *
From A
Full JOIN B
On A.aid=b.bid
Results:
Aid Aname acode Bid bname bcode
1 AA 001 1 AAA 101
2 BB 002 null NULL NULL
3 CC 003 3 BBB 102
4 DD 004 NULL NULL NULL
5 EE 005 5 CCC 103
null NULL NULL 7 DDD 104
null NULL NULL 9 eee
----------------------------------------------------------------------------Cross Join: Cross Join
Returns a Cartesian product of two tables.
Example 1:
SELECT *
From A
Cross JOIN B
Results:
Aid Aname acode Bid bname bcode
1 1
2 1
3 1
4 (5*5 article) 1
5 1
1 3
2 3
......
......
......
Example 2:
SELECT *
From A
Cross JOIN B
WHERE
A.aid = B.bid
Results:
Aid Aname acode Bid bname bcode
1 AA 001 1 AAA 101
3 CC 003 3 BBB 102
5 EE 005 5 CCC 103
As with the inner join result, you are actually returning all the results (25) and then querying the results that match where.
SQL Server [Join] Grooming
