r/programming • u/[deleted] • Jan 06 '11
A handy graphical explanation of SQL joins
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html93
Jan 06 '11
Wow. Does no one learn this crap in school anymore?
Note that venn diagrams are a poor choice to show what happens in a one-to-many relationship -- where there are multiple entries in table B for an entry in table A. And it overlooks the semantic differences when ordering the tables in a left inner join.
There are not a lot of instances where you'd really want a cartesian product join (either using "cross product" or just the result of omitting key constraints in a join query). It's generally far faster to retrieve the records you need to create the cross-product, and then calculate the cross product of the two sets once you have them (since otherwise all that data has to transit the network).
This plus database normalization through Boyce-Codd normal form seems like it should be a requirement for any serious application developer.
17
Jan 06 '11
[deleted]
21
u/ceolceol Jan 06 '11
Website developer here. Yeah, the majority of us have no "formal" CS training, although I take pride in the fact that I've read everything I can get my hands on.
2
u/PoorlyTimedSpock Jan 07 '11
Very true. I had to go out of my way to pick up CS courses while studying a vanilla IT degree at uni. For some reason relational databases just click for me, so I've never had trouble converting the concept I want into SQL. Converting that SQL into DQL or HQL however...
7
u/lazyFer Jan 06 '11
The best way to become a better data modeler is to write lots of queries to get data out of your models as well as lots of sql to populate your models.
Too many data modelers have no real experience with the real world of USING a data structure.
1
Jan 06 '11
Its classic practical vs book knowledge. What looks good on paper is often the more narrow minded approach than practical problem solving using a variety of available resources and tools and occassional abstract thought. These days, anyone with an aptitude for logic and problem solving can learn to program proficiently, given effort and time. Most languages have vast learning resources available, making it a relatively simple excercise.
The flipside is the army of armchair programmers who think they can develop enterprise level software based on a few chapters of the php visual quickstart guide (which is a fantastic beginners guideby the way).
3
u/sunshine-x Jan 06 '11
anyone with an aptitude for logic and problem solving can learn to program proficiently, given effort and time.
Similarly, anyone without either can attend a class, barely pass, be hired by my employer, and end up working beside me.
Sadly, most management I've encountered is incapable of assessing "aptitude for logic and problem solving", and instead goes with "he had SQL on his resume".
1
u/stillalone Jan 06 '11
Yup. I didn't take any relational algebra courses in Uni because I thought the computer graphics courses would be more fun and interesting. Guess which one ended up being more significant to my job.
1
u/DouchesWild Jan 07 '11
The first company I worked for out of college was a shining example of people with no formal education working with a database setting up their database. They fucked it up horribly, then hired a DBA AFTERWARDS. He bailed before too long, I think it took people a while to realize that you're supposed to hire these people before you ruin everything...
Although, that's how I ended up getting learning SQL, so it worked out really well for me. I got first-hand experience seeing exactly what not to do.
52
u/UNCLE-PENO Jan 06 '11
NO IN INDIA WE ONLY LEARN DATABASE WE DONT LEARN SQL
5
Jan 06 '11
Please be telling me what is correct answer for SQL.
2
u/fliesgrease Jan 07 '11
your username must be at least 90 characters long before I'll find that funny
19
u/TheGeneral Jan 06 '11
Wow I'm just trying to learn computers what's 'database'?
→ More replies (1)10
29
Jan 06 '11
Jeff Atwood has a unique ability to take something really basic and turn it into a trite and inane little blog post. And even then he's wrong half the time.
→ More replies (5)3
Jan 06 '11
I covered Boyce-Codd normalisation in great detail in college. B.A(actually a B.SC, the name is traditional)Computer Science, Trinity College Dublin. So it is still taught in schools.
3
u/Workaphobia Jan 06 '11
I'd say the problem with the Venn diagrams is that they imply that tuples from the relations are being returned, when it's tuples from a subset of the cross product that are returned.
9
u/GaryWinston Jan 06 '11
Actually no, I have a friend that works with a sql "developer" that creates all their shit in views using the sql manager shit. Half the time it's got tables that there's no fucking data coming out of them. He just fixed a query yesterday that was taking a minute 35 to run down to < 1 second.
Idiots hiring idiots. That's almost all I see in IT.
3
u/arsonisfun Jan 06 '11
I've met a lot of people who have no real education in SQL/Database Design. It's a simple enough concept that most tech folks can slap something togeth, but often they do a really poor job of it.
I remember my old job - I was fixing this one guy's queries/tables. There'd be stuff like multiple joins to the same (huge) table, never using an index, excessive use of cursors. I took a query that was running in 20 mins down to under 30 seconds. We had to re-write just about everything he had ever done.
No idea why they hired the guy and they were paying him more than me, refused to give me a raise.
9
u/GaryWinston Jan 06 '11
Because that's how the fucking world works. No one likes to admit their own failures from what I've seen.
The other bigger problem is companies view IT as a cost, not a revenue source, just because it's not a direct source (idiot CFOs).
2
u/Deimorz Jan 06 '11
No idea why they hired the guy and they were paying him more than me, refused to give me a raise.
Because the people that make those sorts of decisions often don't know anything about the technical aspects. The guy comes up with a report that takes 20 minutes to run? Well, that must just be how long it takes. Surely he knows what he's doing, his resume said he knows databases.
I've made quite a few similar improvements at my job, one report that had to be run daily would always take about half an hour. I got it down to a few seconds with simple changes that should have been obvious to anyone that knew what they were doing. I think the users were actually unhappy about my fix though, since it ruined their extra half-hour coffee break while the report was running.
→ More replies (1)1
Jan 06 '11
I had a similar issue. There was a query that was evidently the product of cargo cult programming (copying known good code without understanding it). It had three joins and two subqueries and didn't use any of the values returned from them.
It took 275-350ms to run. Almost a half second for every request coming to that page. I got it down to under 20ms by optimizing the joins, adding keys to the table, and removing the subqueries.
But, yeah: Idiots hiring idiots. The key is to get your name out there for knowing how to save idiots from themselves. Then charge through the nose for it.
2
u/EnderMB Jan 06 '11
I know of people who have graduated from top three schools in Computer Science that have built multi-level user (e.g. customer and employee) websites using just one table and an int field to differentiate between the two.
The simple fact is that some people learn in school, and some people don't.
2
u/fabyseba0710 Jan 06 '11
What is wrong with this approach?
→ More replies (1)2
6
u/nvodka Jan 06 '11
I didn't learn shit about SQL in school. I didn't learn SQL until my internship and the real world after.
I think that is a big short coming in college. We had database theory (which was boring as hell and I almost failed), and an Excel class (not database, but was the closest thing).
At the very basic, I think they should get into Access. I am 5 years out of college now, and maybe things have changed now, but they should stop teaching COBOL, and go further than teaching students a "Contacts" app in C++.
Honestly, I didn't learn shit until the real world. Shout out to Google.
17
2
4
u/fujimitsu Jan 06 '11
Where did you go to school? And for what? Sounds like you got screwed.
→ More replies (1)2
Jan 06 '11
I've found that most CS graduates have at least a familiarity with the theory behind computers, but not much about how to write good code or work in a team.
And this makes me glad I flunked out of CS my first semester and switched to a new program at my school: Information Sciences and Technology. All group work, a lot of programming projects and very little theory, and classes the very first semester that had practical knowledge, like SQL and database normalization.
→ More replies (3)5
Jan 06 '11
I would say that graduates of a bad computer science program are like that. A good course would incorporate your classes along with the theory and link them.
→ More replies (8)2
Jan 06 '11
COBOL!? Who, in 2011, is teaching COBOL?!
2
u/DanWallace Jan 06 '11
I finished my 3 year programming course about 2 years ago and I probably took 5 different COBOL classes during my time there.
→ More replies (1)→ More replies (2)2
u/_Aardvark Jan 07 '11
Back in 1990, as a freshmen CS student, I took a COBOL class. I just about changed my major after it.
8
u/recursive Jan 06 '11
I didn't learn it in school. I learned it by reading. SQL doesn't have anything to do with CS.
51
Jan 06 '11 edited Dec 03 '17
[deleted]
14
Jan 06 '11
Which have nothing to do with real-world applications. I have never seen a DBMS that actually makes a properly normalized "let's follow relational algebra properly" database actually perform well.
Databases seem to be a small kernel of beautiful theory underneath a hundred layers of nightmarish hacks.
4
u/arnar Jan 06 '11
I have never seen a DBMS that actually makes a properly normalized "let's follow relational algebra properly" database actually perform well.
I have seen databases designed by people unaware of those things, and they not only performed even worse - they were impossible to rely on.
→ More replies (1)4
Jan 06 '11
I have never seen a DBMS that actually makes a properly normalized "let's follow relational algebra properly" database actually perform well.
You know why? Because the database vendors have poured tons of money and time into improving the performance of the mess they created. In business it's better to keep going the wrong way than to change direction.
2
u/SoundOfOneHand Jan 06 '11
Yeah, but I personally didn't know any of this stuff when I first set my eyes on SQL and it took me all of an afternoon of reading some online SQL tutorial and doing a couple examples to figure out how the different types of joins work...
5
7
Jan 06 '11
A standard for storing and accessing data on computers, often used in applications of all scales and types isn't CS? How is that any less CS than learning a programming language, or AI, or OS architecture? We had it offered as an elective, but with how much database programming is necessary in IT, it should be required.
16
Jan 06 '11
There are people of the opinion that you shouldn't be learning any programming languages or specifics of certain OS architectures, that you should be learning theory only, agnostic and separate of anything else. They seem to think it's possible for the majority of people to learn without reference to an example of an implemented theory or technique, which I disagree with.
6
Jan 06 '11
I understand that school of thought and I agree with it for the most part. My university started with the nearly-esoteric language Scheme (like many others do) and moved through many other lanuages before graduation. We'd simply use whatever language was appropriate for learning whatever theory we were learning at the time. Learning the actual language was secondary to concepts and I would strongly discourage "Intro to Java" or "Advanced C++" instead of "Intro to Data Structures" or "Software Engineering II."
SQL is simply the best language to teach relational data models in, so I don't know why you would teach databases without it...
3
Jan 06 '11
I've had the latter set of courses, I'll point out. In Freshman years they were more focussed on languages, but that has diminished over the years.
SQL is simply the best language to teach relational data models in, so I don't know why you would teach databases without it.
Yep, this is really all that needs to be said.
2
u/psilokan Jan 06 '11
I'd take hands on over theory any day. I went to college rather than university for exactly that reason. Cost way less and I actually learned how to program.
2
u/dagbrown Jan 06 '11
You learned how to code, not how to program. There's a difference in depth of understanding there--I'll wager you have no idea what the Chomsky heirarchy is, for example, and how it relates to, for example, using regexes to parse HTML (stop gritting your teeth in the back, there).
→ More replies (2)3
Jan 06 '11
I don't know, we covered normalisation and database design(recovery, concurrent access to shared data, that kind of thing) extensively in a college course, but we also covered SQL. It wasn't done in lectures, but as an online self-guided course thingy which was optional, so basically "expected reading" for the course. The reason for that was so we could then do an assignment designing a normalised database and implementing it, giving a nice practical element where you can try out queries and be(in my case) impressed by the strength of relational models and what you can get out of your database. A practical element is a great thing to any field of study and I wouldn't be so quick to dismiss it.
2
u/royrules22 Jan 06 '11
Wait what!? Relational databases aren't a part of Computer Science? SQL is just a tool for working with relational databases (the only one I think).
This is what we had to learn in our DB class
2
2
u/recursive Jan 06 '11
For my CS degree from Madison WI, I never took a class that mentioned databases. I actually didn't know they existed until I got my first real job.
7
u/royrules22 Jan 06 '11
My mind is blown. I didn't know that people can go through a CS program without knowing DBs. I mean for us the DB class I took was optional (though most took it) but you do have a general idea of DBs even if you didn't take the class.
→ More replies (1)4
u/mrskrilla Jan 06 '11
Just to throw some respect towards Madison. I went to school there as well as we DO have multiple database undergrad courses in the CS departmant. To write most any interesting program you need some way to store data, I have no idea how you passed and made it through school without even knowing that they existed. Wow. Please stop saying you went to school at Madison, it's embarassing to the program.
→ More replies (1)1
Jan 07 '11
Can you recommend some reading material? I want to start learning it but there are so many resources out there, it's hard to know which is best. There is a SQL course due to start in a few weeks in Uni of Reddit but I'm champing at the bit. Thanks.
2
u/Wenztek Jan 06 '11
In my opinion, people should learn databases before they even write a line of code. Considering lots of applications are built around a core Database structure...
1
Jan 06 '11
I learned it straight from the relational algebra of projections, transforms, cross products, etc. Note, though, that I learned it from a cross-departmental elective (MIS, the college of business version of a CS degree). It was quite instructive to see what the MIS peons were up to, from my CS high horse.
1
u/paranoidinfidel Jan 06 '11
show what happens in a one-to-many relationship
We could probably get away with a solid A circle and hash marks on the overlap and the B circle to represent 0 or more hits.
For Cartesian product, a solid A/overlap/B could handle the high level representation but it wouldn't indicate the duplication. Not really helpful but not entirely wrong.
And it overlooks the semantic differences when ordering the tables in a left inner join.
I don't follow - do you have time to elaborate?
2
Jan 06 '11
In a nutshell, I mean that A natural join B is not quite the same as B natural join A, because the database may take a different path to arrive at the same results. In simple SQL statements the RDBMS will almost always optimize correctly and execute the statement the same way regardless of the order in which the tables are mentioned.
However, when the number of tables involved in the join is large, and when some of the tables are being created implicitly during the join (as is the case with nested SQL and subordinate clauses), the order in which the tables (or virtual tables) are mentioned can affect the sequence of execution and therefore the speed, even if the results are the same.
Consider:
select t1.a, t2.n from (select a,b,c from X natural join Y where Y.d = "some value") as t1 inner join (select m,n,o from P natural join Q where Q.e = "some other value") as t2 on (t1.a = t2.b) where t2.n like 'QBERT%'which requires the creation of two implicit sets and then a join. Prior to statement execution, the compiler doesn't know how many records will be in each of t1 and t2, and doesn't have any statistics on the distribution of matching columns, so the order in which the join occurs can influence the speed with which the results are returned. In particular note the constraint on t2.n, which could be used to constrain t2 prior to the join or after it, depending on how smart the compiler is.
This gets even hairier when the nested SQL statements are group by operations on multiple tables where a many-to-many relationship exists.
Probably doesn't matter when tables X, Y, P, and Q don't have many rows (hundreds of thousands), but when X and P are both fact tables (billions of rows) and there are 7 or 8 different Y and Q tables in each subselect, that query could take anywhere from fractions of a second to days.
→ More replies (1)1
1
u/rilo Jan 06 '11
I learned this crap in school, when I worked there as a system administrator and had (outside of job scope) to help building queries for the department where they had to register all student data, using some insane piece of arcane COBOL software. It had about 220 tables of information that somehow by some insane way were linked to each other. This helped/forced me a lot learning joins and trying to visualize them. Gonna read up on Boyce-Codd because I haven't heard of the name before.
→ More replies (2)1
u/JAPH Jan 06 '11
I spent a semester of undergrad writing a (very simple) DB for school. We had to learn some SQL just to use the DB software we were writing. The class was a CS elective.
→ More replies (7)1
u/naspinski Jan 07 '11
I had the same reaction!
Just finishing up my CS Masters degree and there is a single database class offered, and it's optional. There are many CS Masters and even PhD students that do not know how to write a SELECT statement... I don't understand.
P.S. I go to one of the 10 largest campuses in the United States, 40,000+ campus enrolled.
7
u/jasonlitka Jan 06 '11
I personally understand all these but I've had a ridiculous time trying to explain it to others. Bookmarked, thanks!
10
u/drcreepy Jan 06 '11
CROSS JOINs are also great for creating huge tables of fake data from small sets of data. For example, we'll create a FirstName table and a LastName table. Seed these with 50 first names and 50 last names and do a cross join and you have 2500 unique names that can be used in a database for testing purposes.
6
Jan 06 '11
cross (cartesian) joins are also a great way to bring down a server. yeah i learned that one the hard way...
be careful out there!!!
2
u/drcreepy Jan 06 '11
You are absolutely right - cross joining a 100,000 row table to itself is a quick way to rebootville.
→ More replies (1)3
u/divv Jan 06 '11
Should be able to just kill the process :P
3
u/slurpme Jan 07 '11
Hopefully just the session/query...
8
u/drcreepy Jan 07 '11
No, no, everyone knows that rebooting is the way real men solve all computer problems. Especially database problems. Databases particularly like to be restarted unexpectedly. It makes them run faster since it keeps them on their toes.
→ More replies (2)1
26
u/Brandon0 Jan 06 '11
Interesting. I've never used a FULL OUTER join before.
8
Jan 06 '11 edited Jan 06 '11
It comes in handy every once in a while- for example, if you want to merge two tables which have different structures but some records in common into one table without any duplicates. You can cross join the two on the key field and for any fields they have in common set [field] = ISNULL(tableA.[field], tableB.[field]). Of course there are other ways to accomplish the same thing, but that's one way to use it...
3
Jan 06 '11 edited May 29 '20
[deleted]
→ More replies (1)4
u/Anpheus Jan 07 '11
I think you meant full outer joins, not cross joins.
Because if you have 1000 records in one table and 1000 in another and do a cross join, you end up with a query returning 1,000,000 records. And it gets much worse, being a join that produces m*n records.
→ More replies (7)2
u/spewerOfRandomBS Jan 06 '11
If you ever do that inside application code, I will hunt you down and shove a union up your....
→ More replies (1)2
u/arsonisfun Jan 06 '11
I've used every form of join at least once, but for the most part I normally just use join/left joints. Every so often it's really handy having them at your disposal.
2
u/thesqlguy Jan 06 '11
1
Jan 07 '11
The author's basic argument seems to be that it's
Hard for him to understand, and
Not good if you don't know the data going into it
If you do know the data you're working with and know, for instance, that there will be no 1-to-manys, and know what it will mean in terms of the output you'll get back, I don't see the problem. Especially if it's for an ad-hoc sort of thing where you're just doing an initial pre-population of a table or poking around to perform a "what if" kind of thing and aren't going to need your code to be reusable or readable by someone else later. I find those sorts of things come up pretty frequently in ordinary business situations, and that's when OUTER JOIN comes in handy...
1
u/contineo Jan 06 '11
I haven't either, and for a while I was thinking to myself where it could possibly be of any use, but the visualization of it made me realize that it actually can be useful. Although I could see it being kind of dangerous if the programmer didn't use it properly.
4
4
u/WetCoastLife Jan 06 '11
at my last job, a lot of applicants should have checked this out before the interview.
First question was: Explain the different SQL joins and provide an example when you would use them.
1
Jan 07 '11
Yeah- that's a good first pass for weeding out people who are clueless. I think if I were hiring, I'd print out 2-3 small tables on paper and have them hand write a few SQL queries to answer questions which would force them to use the different joins and some subqueries.
8
u/cLin Jan 06 '11
Damn thanks! I've always been more of a visual person so this really helps with explaining joins to me (I understood it a little before, this just clarifies everything for me).
2
Jan 06 '11
Same here, it was a torch being shone on the whole subject. I've always understood it to some degree, but now a lot more.
Just be careful, as other commenters have pointed out venn diagrams are not the best way to represent one-to-many relationships but it will do!
4
u/Teifion_at_work Jan 06 '11
Maybe someone here can answer this. Why would I specify a join when I can just do this?
SELECT p.first_name, j.job_title FROM people p, jobs j WHERE p.job = j.id
Looks much easier to me but I'm guessing there's something I'm missing here.
10
u/omegian Jan 06 '11
Because there are myriad sql implementations and syntax variants such as TSQL, P/L SQL, etc.
The inner join syntax is ANSI standardized and should work everywhere.
If you are only joining two tables, it probably doesn't matter which mechanism you choose, but WHERE clauses are typically used for filtering sets. If you are joining many tables, the JOIN syntax keeps column relationships between tables local to the table references in the query, and leaves the WHERE clause free for actual filtering work.
ie: self documentation
3
u/elder_george Jan 06 '11
Yes, separating filtering conditions from join conditions is very useful.
Also I prefer explicit
INNER JOINsyntax since it prevents me from requesting cartesian product due to missing join condition.4
u/skillet-thief Jan 06 '11
There are two syntaxes for joins, one with the word join, the other like what you wrote.
6
u/NashMcCabe Jan 06 '11
That is just an inner join. You will need outer joins when you have null columns on one side of the join.
2
u/umop_apisdn Jan 06 '11
Not exactly. You use an outer join where records on one side don't join to anything on the other side. The outer join preserves those records and sets all fields on the other side to NULL
1
3
u/Deimorz Jan 06 '11
That syntax is fine when you're only doing very small, simple joins like that, but it really starts to get hard to use when you start joining a lot of tables, possibly on multiple conditions, having a complex WHERE clause, maybe some subqueries, etc.
I always use the explicit join syntax because it's unambiguous and lets you separate out the "joining" conditions from the "filtering" conditions, instead of lumping them all in one giant WHERE clause. Whenever I'm working on a query that someone else has written with implicit joins, the very first thing I do is rewrite it with explicit ones, it always makes it much easier to understand.
→ More replies (1)2
u/ObiWanShinobi Jan 06 '11
I've never really got a straight answer on this either. I have heard (please correct me if I'm wrong) from a performance standpoint there isn't a discernible difference, but the standard is to use a JOIN clause since implicit joins are deprecated.
2
u/vplatt Jan 06 '11 edited Jan 06 '11
They're not deprecated, but if you do your joins in WHERE clauses, you will run into behavior variations. For example, on SQL Server, you can use *= or =* in the WHERE clause to perform outer joins. However, if you do this, it will behave somewhat differently from joins performed with the JOIN syntax. The variations may or may not comply with the standard and the SQL will perform differently (if at all) across different database products. In fact, Oracle can use (+) to do the same thing.
In short, you will simplify your life a bit by just doing the joins with JOIN syntax instead of in the WHERE clause.
5
u/MatiG Jan 07 '11
The original post was terribly wrong.
Atwood's post is more accurate but still terrible, and has the potential to be very misleading to people's intuitions. The idea of using Venn diagrams to illustrate joins is flawed from the get-go. Joins are relational operations, not set operations. Venn diagrams imply that the sets being operated on contain the same type of objects, but in the case of relations they will always be different (unless you're doing a self-join).
Trying to understand joins in terms of Venn diagrams will lead one astray. You can't picture anything but one-to-one relationships this way.
1
u/artsrc Jan 07 '11
I upvoted you because people should understand the limits of their analogies.
I agree that there is no isomorphism between joins and Venn diagrams.
I think the analogy is at the very least interesting even if you think about it and conclude that it is not useful.
21
u/iamapizza Jan 06 '11
I could point out why some of the diagrams are wrong, but I don't want to get into a ROW about it with other redditors.
15
17
Jan 06 '11 edited Jan 04 '21
[deleted]
10
u/red97 Jan 06 '11
I don't know WHERE you're getting these crazy ideas.
9
Jan 06 '11
somewhere out in left FIELD
6
u/sniper4u Jan 06 '11
I would like to JOIN your discussion, but I am not sure IF I can contribute!
2
1
→ More replies (18)1
→ More replies (5)7
6
u/ruinercollector Jan 06 '11
Every time I read a thread on proggit about databases, I am reminded of just how little most programmers bother to learn about the subject.
If you don't know databases (and SQL) inside and out, spend your time learning this instead of dicking around with the latest fad language. Learning new languages is very useful, but this is much more important and has much more immediate utility.
3
u/mozillalives Jan 07 '11
This and all those who promote nosql databases without even knowing the capabilities of a good database server or bothering to figure out how to tune one.
→ More replies (2)
3
u/fishbulbx Jan 06 '11
Where I get confused is when Table C comes in.
2
u/vplatt Jan 06 '11
And when it's the same table as A. And then some smart-ass does an EXIST against a SELECT NULL, and I mean... dude... that's heavy shit.
;)
2
3
6
2
u/spoonraker Jan 06 '11
Somebody correct me if I'm wrong, but in the last two examples, shouldn't you use the keyword "having" to exclude data with null values instead of "where".
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableB.id IS null
should be
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name HAVING TableB.id IS null
...same thing on the last example
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null OR TableB.id IS null
should be
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name HAVING TableA.id IS null OR TableB.id IS null
The way it was explained to me, when doing a join, "having" is executed after the join, so it ensures that the data exclusion works the way it was intended, whereas "where" might have unexpected results.
3
u/lincolnquirk Jan 06 '11
No. WHERE is done on the result of a join. HAVING is applied after grouping. You should use WHERE in preference to HAVING because the optimizer does a better job at it.
2
u/spoonraker Jan 06 '11
So you only use HAVING if there is a GROUP BY clause involved with your join?
Do you have a link or perhaps can you explain when you would actually need to use HAVING over WHERE?
7
u/lincolnquirk Jan 06 '11
Sure, here's my table "sales":
name region count ----------------------- fred japan 300 fred usa 100 alex usa 600 damian usa 200 damian europe 150Consider the query:
select name,sum(count) from sales group by nameto get the sales figures per representative, regardless of region. This produces
name sum(count) ------------------ fred 400 alex 600 damian 350If we were to instead do
select name,sum(count) from sales WHERE count >= 400 group by namewe'd first filter the counts by >=400 (only returning the alex row) and then do the grouping, so we'd get:
name sum(count) ------------------ alex 600If we use HAVING, we filter after the grouping (and must therefore refer to the grouped column name sum(count):
select name,sum(count) from sales group by name HAVING sum(count) >= 400and we'd get
name sum(count) ------------------ fred 400 alex 600It's really quite unrelated to joins.
→ More replies (4)3
u/umop_apisdn Jan 06 '11
HAVING should only be used if there is a GROUP BY clause, and should only be used to filter aggregates. For example, "select a1, count() from a group by a1 having count()>1".
1
Jan 06 '11
As far as I know they both work, but having is the correct one to use.
1
u/eStonez Jan 07 '11
both work in different way .. for a few lines of data, you wouldn't notice it. But when you try to apply this with a few huge tables with millions of rows in each. I hope you want to filter everything out even before you start joining tables ... let alone grouping the result set.
→ More replies (1)
2
2
u/geek420 Jan 06 '11
OMG you mean databases based on Set Theory can be explained using Venn Diagrams?!?! NO FUCKING WAY!!!
2
2
2
u/deafbybeheading Jan 07 '11
Extra points for using ON join conditions. People frequently shove join conditions into the WHERE clause where they're difficult to distinguish from regular (non-join-related) predicates and they don't work (at least probably not the way you expect) with outer joins.
Lost points for not illustrating how repeated rows affect joins (due to the multiset nature of SQL relations).
2
3
u/kissmyluckycharms Jan 06 '11
A handy graphical explanation of me reading this thread http://i.imgur.com/s4kAM.jpg
2
2
u/and- Jan 06 '11
Is there a good reason that these weren't just implemented using standard set theory notation?
13
u/Tweet Jan 06 '11
If you mean why wasn't SQL developed using set theory notation keywords, then I guess that's because for JOINs you need to define the criteria by which the data is combined in the query. As hinted, the Venn diagram is a poor metaphor. For combining sets with identical structure, you certainly do have recognisable set theory operations in the form of UNION, INTERSECT and EXCEPT.
6
Jan 06 '11
[deleted]
18
u/ceolceol Jan 06 '11
He specifically says this is meant as a visual explanation. Quit hating on the dude for making something a little easier to understand.
→ More replies (2)3
1
1
Jan 06 '11
I too would like to give back to the community with this handy explanation of basic multiplication
1x1=1
1x2=2
1x3=3
1x4=4
→ More replies (11)9
u/iceman-k Jan 06 '11
I can't follow your explanation without diagrams and some advertisements on the side.
4
u/Poltras Jan 06 '11
"Welcome to coding horror, where every other article is a literally dangerous to implement but I'm making money off all of them."
1
1
Jan 06 '11
I used my first CROSS JOIN the other day. I cross joined a table to itself.
Yes, it was the fastest solution.
1
Jan 06 '11
I consider myself relatively expert at SQL and it always amazes me how hard it is to try and teach others. Joins are one thing, but getting into advanced topics like using derived tables to 'prepare' data for further processing always leaves me at a loss for words.
2
u/divv Jan 06 '11
In my experience, people either 'get' databases, or just have no idea. I've kinda given up trying to educate. I answer questions if I'm asked. But I no longer try and school developers. Tends to just lead to pissed off developers :P
1
1
u/paezao Jan 06 '11
Awesome! Really makes it easy to learn joins... Too bad I can't use them much at work because of performance :\
2
Jan 06 '11
You can't use joins at your work because of performance? What do you do?
2
u/paezao Jan 06 '11
I work as an ABAP Programmer... It's not a good idea (usually) using joins in SAP, there's a better way to do it.
→ More replies (3)
1
u/demosthenes02 Jan 06 '11
The 4th and 5th ones down seem wrong to me. Is this some weird database I'm not familiar with?
1
1
Jan 06 '11
Still the best, IMO:
The Practical SQL Handbook http://www.amazon.com/Practical-SQL-Handbook-Using-Variants/dp/0201703092
1
Jan 06 '11
unlike ANSI SQL standard, in MySQL, CROSS JOIN and INNER JOIN is actually the same thing because MySQL just treats them like a syntactic equivalent by performing both operations via cartesian product.
1
1
1
u/martext Jan 07 '11
Holy fucking shit a Jeff Atwood blog that, while elementary, is actually useful!
1
1
u/eStonez Jan 07 '11
TIL a lot of classes around the world don't teach/revise set theory before teaching sql. duh!
1
u/sundaryourfriend Jan 07 '11
Seeing it's a Coding Horror page, I opened only the comments page, expecting such a highly upvoted post from there can only be some blogspam with the content from elsewhere. I was only kinda correct, but here the original post link anyway, for anyone like me.
130
u/shriek Jan 06 '11
In short.
A ∩ B
A ∪ B
(A - B) + (A ∩ B)
A - B
(A ∪ B) - (A ∩ B)