dslreports logo
site
 
    All Forums Hot Topics Gallery
spc

spacer




how-to block ads


Search Topic:
uniqs
1772
share rss forum feed


loli
Premium
join:2002-08-26
South Richmond Hill, NY

mysql practice question

Hello. My semester is over and I'm studying sql for next semester ahead of time. This is not homework! I promise. I've reached this point that's driving me crazy.

I have 2 tables and I want to update the sum value as pictured below. Is the query okay btw? I understand I can just do

SELECT SUM( table1.x + table2.x + table1.y + table2.y ) AS sum
FROM table1, table2
WHERE table2.name =  'a'
 

But I want to understand the different kinds of JOINS I guess.

So yeah. I want to update sum so that for name = a, it is 330. name = b, it is 370 and so on. I'm supposed to use some form of nested query here?




Sites I use for practice
»www.sqlcourse.com/index.html
»www.sqlcourse2.com/index.html
»sqlzoo.net/

Any recommendations would be good too :)


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7

1 recommendation

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
The number of resulting rows is the product of the number of rows from each table. A CROSS JOIN can produce gigantic result sets inadvertently so be careful.

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 first example is the proper way to get 330 for record 'a' presuming name = a would appear only once in table2. If a appeared multiple times, all 'a' rows would be added together.

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.


loli
Premium
join:2002-08-26
South Richmond Hill, NY
reply to loli
wow... Didn't expect all that Give me a moment while I absorb this. Thanks alot!

I think I also figured out how to deal with my updating question.
--
Verizon FiOS 35/35 »bit.ly/RVzC75


cdru
Go Colts
Premium,MVM
join:2003-05-14
Fort Wayne, IN
kudos:7
said by loli:

I think I also figured out how to deal with my updating question.

Are you updating a table and saving the sum?

Good database practice is normalizing data so that you minimize redundancies and dependencies. Having a computed field such as your sum generally would not go along as a best practice since it's a computed value. There can always be exceptions to this, particularly if a given computation is complex or to gain performance advantage.


loli
Premium
join:2002-08-26
South Richmond Hill, NY
said by cdru:

said by loli:

I think I also figured out how to deal with my updating question.

Are you updating a table and saving the sum?

Yeah. Just for practice purpose I'm trying to come up with simple problems to deal with updating and nested queries. I've seen selects inside selects or something of that nature.

Normalizing I get a sense of it's usage. »www.sqlcourse2.com/joins.html
--
Verizon FiOS 35/35 »bit.ly/RVzC75


loli
Premium
join:2002-08-26
South Richmond Hill, NY
reply to loli
Damn. I see myself toying around with this all month long. This is fun. Thanks alot again for the quick and easy to understand examples. I figured out subqueries too

watice

join:2008-11-01
New York, NY
reply to cdru
wow, I was struggling with JOINS myself & had asked elsewhere recently for help. Thank you kindly. Will be reading this in detail once I am not so sleep deprived from all night coding binges.