CODEWITHSUNDEEP
mysqlsqljoincomputer-science
python
python

Reputation: 4521

How the SQL join actually works?

Suppose I have two table Gardners table and Plantings table.

enter image description here

Suppose my query is:

SELECT gid, first_name, last_name, pid, gardener_id, plant_name 
FROM Gardners
INNER JOIN Plantings
ON gid = gardener_id

I want to know how exactly it works internally?

As per my understanding in every join condition:

In terms of program if you think:

for i in [Gardners Table]:
    for j in [Plantings Table]:
         if i.id == j.garderner id:
            print <>

Now suppose if you query is something like:

User(uid,uname,ucity,utimedat)
Follows(uid,followerid) // uid and followerid are pointing to `uid` of `User`.

SELECT U.uid, U.uname FROM User U1 JOIN Follows F,User U2 WHERE 
  F.followerid = U2.uiddate AND U2.ucity = 'D'

How the join condition will work internally here? Is it equivalent to:

for i in [USER]:
        for j in [Follows]:
            for k in [USER]:
             if condition:
                print <>

Upvotes: 2

Views: 3233

Answers (3)

Iurii Tkachenko
Iurii Tkachenko

Reputation: 3189

Your example with Gardners table and Plantings table is correct. But example with users not so obvious.

I think that what you want to get is user followers from some city.

Assuming correct query is:

SELECT U1.uid, U2.uname
FROM User U1 
  JOIN Follows F ON U1.uid = F.uid
  JOIN User U2 ON F.followerid = U2.uid
WHERE U2.ucity = 'D'

Then in pseudo code it'll look like this:

for i in [User Table]:
  for j in [Follows Table]:
    if i.uid = j.uid:
      for k in [User Table]:
        if j.followerid = k.uid and k.city = 'D':
          print <>

SQL Fiddle for this: http://sqlfiddle.com/#!9/caeb1e/5

There is a very good picture of how joins actually works can be found here: http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

SQL Joins

Upvotes: 3

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

In your second query, it's not clear what you're trying to do exactly as the syntax is erroneous; but if I were to guess, it seems like your intention is to join User U1 with a sub query of (implicit) join between Followers F and User U2.

If my guess is correct, the query would properly look more like this:

SELECT U1.uid, U1.uname 
FROM User U1 JOIN 
  (SELECT U2.uid 
   FROM Followers F,User U2 
   WHERE F.followerid = U2.uiddate AND U2.ucity = 'D') T 
WHERE u1.uid = T.uid

Which is not a 'best practice' way of writing the query either (you should use explicit joins, there's no need for a sub-query but you can just join three times, and so on) But I wrote it this way to keep it closest to your original query.

And if my guess is correct, then your pseudo code would be more like:

for u2 in [User 2 where condition]:
        for f in [Follows]:
           if f.uid == u2.uid
             SELECT uid AS T
               for u1 in [User 1]:
                 if u1.uid == T.uid:
                   print <>

However, it's not a fully explained interpretation, because one key to understanding SQL is to think more in 'set' of data being filtered, rather than sequential selection of objects of data, because SQL does operations based on the set of data, which one might not be used to. So a number of the above steps will be executed in one go, instead of sequential. But other than that, you should look towards the answer given by Yuri Tkachenko above, in how to view joins - and then the internals will come second when writing correct joins.

Upvotes: 1

Tonton Sevilla
Tonton Sevilla

Reputation: 11

Yes you're understanding is correct if you are only talking on a join not on the other join eg: Inner, Outer like in SQL

Upvotes: 0

Related Questions