CODEWITHSUNDEEP
mysqlsql
AM-
AM-

Reputation: 881

Learning SQL: UNION or JOIN?

Forgive me if this seems like common sense as I am still learning how to split my data between multiple tables.

Basically, I have two:

I wish to fetch the data from them and cannot decide how I should do it: in a UNION:

SELECT `userID`, `owner`, `server`, `name`
FROM `english`.`general` 
WHERE `userID` = 54 LIMIT 1 
UNION
SELECT `posts`, `topics` 
FROM `english`.`count` 
WHERE `userID` = 54 LIMIT 1

Or a JOIN:

SELECT `general`.`userID`, `general`.`owner`, `general`.`server`, 
       `general`.`name`, `count`.`posts`, `count`.`topics` 
FROM `english`.`general` 
JOIN `english`.`count` ON 
     `general`.`userID`=`count`.`userID` AND `general`.`userID`=54 
LIMIT 1

Which do you think would be the more efficient way and why? Or perhaps both are too messy to begin with?

Upvotes: 0

Views: 185

Answers (4)

Andrew Richards
Andrew Richards

Reputation: 59

Just for completeness as I don't think it's mentioned elsewhere: often UNION ALL is what's intended when people use UNION.

UNION will remove duplicates (so relatively expensive because it requires a sort). This remove duplicates in the final result (so it doesn't matter if there's a duplicate in a single query or the same data from individual SELECTs). UNION is a set operation.

UNION ALL just sticks the results together: no sorting, no duplicate removal. This is going to be quicker (or at least no worse) than UNION.

If you know the individual queries won't return duplicate results use UNION ALL. (In fact often best to assume UNION ALL and think about UNION if you need that behaviour; using SELECT DISTINCT with UNION is redundant).

Upvotes: 1

Phoebus
Phoebus

Reputation: 661

If you want to fetch users and near user posts and topics. you need to write QUERY using JOIN like this:

SELECT general.*,count.posts,count.topics FROM general LEFT JOIN count ON general.userID=count.userID

Upvotes: 0

zerkms
zerkms

Reputation: 254906

It's not about efficiency, but about how they work.

UNION just unions 2 different independent queries. So you get 2 result sets one after another.

JOIN appends each row from one result set to each row from another result set. So in total result set you have "long" rows (in terms of amount of columns)

Upvotes: 2

Ray
Ray

Reputation: 41428

You want to use a JOIN. Joining is used to creating a single set which is a combination of related data. Your union example doesn't make sense (and probably won't run). UNION is for linking two result sets with identical columns to create a set that has the combined rows (it does not 'union' the columns.)

Upvotes: 0

Related Questions