LEFT JOIN....RIGHT JOIN.....INNER JOIN? What join do I use?

by Joy Nelson on Sep 21, 2012

Koha offers its users a very strong reporting tool. In reports you can quite easily create your own SQL queries to pull your own data out of Koha. If you’ve taken advantage of the Koha Reports Library you’ve probably run across some fairly complex jargon in some of the SQL statements. And some of the most confusing SQL language relates to joining one table to another.

Left Join….Right Join….Inner Join.

Sounds a bit like the hokey pokey! And in some ways it is very similar to a dance. Joins help you specify exactly which bits of data you want to dance with in the query you are constructing. Let’s create some examples to illustrate how they work. First let’s construct two practice tables.

TABLE a

TABLE b

Put Your LEFT JOIN in…..

Select * from a LEFT JOIN b on a.name=b.name

The result? A list of all the rows in table a will be displayed and for each row displayed the matching values in table b will be shown. If no values are in table b, then a NULL value is shown. This join is very useful for showing missing data in the “joined” table. Simply look for the NULL.

Now Put Your RIGHT JOIN in…..

Select * from a RIGHT JOIN b on a.name=b.name;

The result? Select all the rows from the ‘right’ table (table b) and for each row display the matching value in table a or a NULL value.

Put Your INNER JOIN in ….

SELECT * from a INNER JOIN b on a.name=b.name

The result? Select all the rows in Table a that have a match in Table b. This is a great way to see what bits of data exist across tables for each record.

I hope this provides you a start on understanding JOINS. They can be quite powerful and we have only scratched the surface of the power of JOINS in this blog post. So put on your dancing pumps and have a go around with some JOINS and see what kind of dance partner they are!