Andrew Mobbs (mobbsy) wrote,
Andrew Mobbs

An explanation of SQL joins

I was slightly surprised on IRC yesterday that the Venn diagram explanation of SQL joins wasn't more widely known, so I thought this might be worth a blog post:

Suppose we have two tables, with a bit of data:

                field1 NUMBER,
                field2 VARCHAR(255),
                field3 DATETIME);
INSERT INTO A VALUES ('red',42,'foo','30-11-1975');
INSERT INTO A VALUES ('blue',23,'bar','01-01-2000 00:00');
INSERT INTO A VALUES ('green',17,'wibble','19-01-2038 03:14:07');
INSERT INTO A VALUES ('yellow',5,'teapot','21-12-2012');

                field1 VARCHAR(255),
                field2 NUMBER);
INSERT INTO B VALUES ('blue','Monday',88);
INSERT INTO B VALUES ('green','gills',1);
INSERT INTO B VALUES ('purple','prose',99);

Those two tables can be considered as sets, each element of the sets being a tuple:

A={ (red,42,foo,30-11-1975) , (blue,23,bar,01-01-2000 00:00) , (green,17,wibble,19-01-2038 03:14:07) , (yellow,5,teapot,21-12-2012) }

B={ (blue,Monday,88) , (green,gills,1) , (purple,prose,99) }

An inner join operation in SQL takes a predicate that determines which parts of the tuples that comprise each set are being considered, and returns the required rows:

SELECT a.key,a.field1,b.field1 FROM a INNER JOIN b ON a.key=b.key
returns the set { (green,17,gills) , (blue,23,Monday) }

The rows returned are those matching the intersection of the set of keys.
(N.B. This differs from an SQL INTERSECT operation in that that considers all elements from two sets, not just those named in the predicate.)

A left outer join returns all elements matching the predicate from the left-hand set, and replaces any missing data from the other set with NULL:

SELECT a.key,a.field1,b.field1 FROM a LEFT OUTER JOIN b ON a.key=b.key
returns the set { (red,42,NULL) , (blue,23,Monday) , (green,17,gills) , (yellow,5,NULL) }

A right outer join is similar, but with the roles of the sets reversed:

SELECT a.key,a.field1,b.field1 FROM a RIGHT OUTER JOIN b ON a.key=b.key
returns the set { (blue,23,Monday) , (green,17,gills) , (purple,NULL,prose) }

A full outer join combines the results of the left and right outer joins, returning all keys, and replacing any missing values from either side with NULL:

SELECT a.key,a.field1,b.field1 FROM a FULL OUTER JOIN b ON a.key=b.key
returns the set { (red,42,NULL) , (blue,23,Monday) , (green,17,gills) , (purple,NULL,prose) , (yellow,5,NULL) }

  • (no subject)

    Last week I poured the cremated remains of my father into a river. From there, that material will flow through the town he lived in, into the sea,…

  • Moving house!

    We're moving house soon… details to follow in a less public post, or email me. However, we're getting rid of some bits and pieces of…

  • (no subject)

    Yesterday, I made sausages. This was sufficiently exciting to cause me to actually write something on LJ for once. One of our wedding gifts was a…

  • Post a new comment


    default userpic

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.