 cdruGo ColtsPremium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7 | There are 3 main types of JOINs in SQL: INNER, OUTER, and CROSS. In addition, outer can be split up into LEFT OUTER, RIGHT OUTER, and FULL OUTER. Below are examples of 6 types of joins.
Here is the data that we'll be using for the examples, just so we're on the same page:
sql code: CREATE TABLE [dbo].[table1](
[name] [nvarchar](1) NULL,
[x] [int] NULL,
[y] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table1] ([name], [x], [y]) VALUES (N'a', 100, 200)
INSERT [dbo].[table1] ([name], [x], [y]) VALUES (N'b', 200, 400)
INSERT [dbo].[table1] ([name], [x], [y]) VALUES (N'e', 400, 800)
CREATE TABLE [dbo].[table2](
[name] [nvarchar](1) NULL,
[x] [int] NULL,
[y] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[table2] ([name], [x], [y]) VALUES (N'a', 10, 20)
INSERT [dbo].[table2] ([name], [x], [y]) VALUES (N'b', 30, 40)
INSERT [dbo].[table2] ([name], [x], [y]) VALUES (N'c', 50, 60)
INSERT [dbo].[table2] ([name], [x], [y]) VALUES (N'd', 70, 80)
INNER: An inner join will take values from both the left and right tables from the JOIN based on some criteria. Typically this is some type of a key field although that is not a requirement that it be a true key.
sql code:SELECT table1.name as name1,
table1.x as table1x,
table1.y as table1y,
table2.name as name2,
table2.x as table2x,
table2.y as table2y
FROM table1
INNER JOIN table2 on table1.name = table2.name
name1 table1x table1y name2 table2x table2y
a 100 200 a 10 20
b 200 400 b 30 40
In the above example, the INNER JOIN is performed by matching rows where table1.name = table2.name. Only rows A and B from each table are returned as those are the only two names that are common between both tables.
LEFT OUTER: A left outer join will take all rows from the left table and only the rows from the right table that match based on the criteria.
sql code:SELECT table1.name as name1,
table1.x as table1x,
table1.y as table1y,
table2.name as name2,
table2.x as table2x,
table2.y as table2y
FROM table1
LEFT OUTER JOIN table2 on table1.name = table2.name
name1 table1x table1y name2 table2x table2y
a 100 200 a 10 20
b 200 400 b 30 40
e 400 800 NULL NULL NULL
For the e row, we get NULL values for the table2 columns from table2 as there was not a corresponding row in that table, but the LEFT OUTER requires that all the rows from the left table be included.
RIGHT OUTER: A RIGHT OUTER is just like a LEFT OUTER, except all the rows from the right table are included even if there are corresponding rows in the left table.
sql code:SELECT table1.name as name1,
table1.x as table1x,
table1.y as table1y,
table2.name as name2,
table2.x as table2x,
table2.y as table2y
FROM table1
RIGHT OUTER JOIN table2 on table1.name = table2.name
name table1x table1y table2x table2y
a 100 200 10 20
b 200 400 30 40
NULL NULL NULL 50 60
NULL NULL NULL 70 80
We have two new rows that correspond to c and d in table2. NULL shows up as the name as we specify that we want to use table1.name as the name.
FULL OUTER: A FULL OUTER join combines both the LEFT and RIGHT OUTER JOINs, meaning it includes all rows from both tables regardless if a corresponding row is found in the other.
sql code:SELECT table1.name as name1,
table1.x as table1x,
table1.y as table1y,
table2.name as name2,
table2.x as table2x,
table2.y as table2y
FROM table1
FULL OUTER JOIN table2 on table1.name = table2.name
name1 table1x table1y name2 table2x table2y
a 100 200 a 10 20
b 200 400 b 30 40
e 400 800 NULL NULL NULL
NULL NULL NULL c 50 60
NULL NULL NULL d 70 80
CROSS JOIN: A CROSS JOIN does not use a ON clause for joining the two tables. With a CROSS JOIN, every row in the left table is joined to every row in the right table.
sql code:SELECT table1.name as name1,
table1.x as table1x,
table1.y as table1y,
table2.name as name2,
table2.x as table2x,
table2.y as table2y
FROM table1
CROSS JOIN table2
name1 table1x table1y name2 table2x table2y
a 100 200 a 10 20
a 100 200 b 30 40
a 100 200 c 50 60
a 100 200 d 70 80
b 200 400 a 10 20
b 200 400 b 30 40
b 200 400 c 50 60
b 200 400 d 70 80
e 400 800 a 10 20
e 400 800 b 30 40
e 400 800 c 50 60
e 400 800 d 70 80
With your original example where you just had "FROM table1, table2", that is shorthand for a CROSS JOIN. You happened to get the expected result because table1 only had 1 row. When it was CROSS JOINed to table 2, there was only 1 row that had table2.name = 'a'. With my example data, you would have 3 rows, one for table1.a, table1.b, and table1.e and you'd get a very different result (2190 vs 330).
What you would want to do is a INNER JOIN or RIGHT INNER JOIN.
sql code:SELECT SUM( table1.x + table2.x + table1.y + table2.y ) AS sum
FROM table1
INNER JOIN table2 on table1.name = table2.name
WHERE table2.name = 'a'
sum
330
SELECT SUM( table1.x + table2.x + table1.y + table2.y ) AS sum
FROM table1
LEFT JOIN table2 on table1.name = table2.name
WHERE table2.name = 'c'
sum
NULL
SELECT SUM( ISNULL(table1.x, 0) + table2.x + ISNULL(table1.y, 0) + table2.y ) AS sum
FROM table1
RIGHT JOIN table2 on table1.name = table2.name
WHERE table2.name = 'c'
sum
110
The second example is looking at row c, which does not have a corresponding row in table 1. That's why we get a NULL value.
The third example fixes the second example by wrapping the table1 values in an ISNULL() function. Thsi function will use the 2nd parameter as the value if the first parameter is NULL. |
·
Give me a moment while I absorb this. Thanks alot!