Koha How-To

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

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

NAME COMPANY
Bo Peep SheepRUs
Boy Blue Pie
Ma Hubbard Daycare
Jack & Jill Water Delivery

TABLE b

NAME PHONE
Bo Peep 111-9876
Boy Blue 222-9832
3 Kittens 333-2121

 

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.

Bo Peep SheepRUs Bo Peep 111-9876
Boy Blue Pie Boy Blue 222-9832
Ma Hubbard Daycare NULL NULL
Jack & Jill Water Delivery NULL 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.

Bo Peep SheepRUs Bo Peep 111-9876
Boy Blue Pie Boy Blue 222-9832
NULL NULL 3 Kittens 333-2121

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.

Bo Peep SheepRUs BoPeep 111-9876
Boy Blue Pie Blue 222-9832

 

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!

Read more by Joy Nelson

Tags reports tutorial