Main Content

Ruby / SQLite3 example program, showing JOIN v LEFT JOIN

Archive - Originally posted on "The Horse's Mouth" - 2013-02-16 13:49:54 - Graham Ellis

Ruby includes support for the SQLite3 database ... and SQLite is an excellent tool for developing and testing ("Spike solutions") your database structure and concepts before implementing a larger system. For a smaller and straightforward requirement, SQLite may be all that you require.

Towards the end of the Ruby course that ran last week, I demonstrated how simple it is to connect to a SQLite database, insert data and select data ... and I have it all rolled into a single example - full source code [here].

Here are the basics ...
Connect to the database:
  require 'sqlite3'
  db = SQLite3::Database.new( "data.db" )
Insert a record:
  db.execute 'insert into aisles values (2,"Sweeties")'
Select and iterate through a result set:
  db.execute( "select * from aisles" ) do |row|
    p row
  end
Close the connection:
  db.close

I've also coded a slighly more complex example - [here] - in which I created two tables, and used joins to connect them together. In my example, I have a shop with a number of aisles, each of which has a number of products on it. If you visit the example link, you'll find sample output too showing this in action

• If I select from both tables, I get all possible joined records out - so with 3 aisles and four products in my example, this gave me 12 rows. Clearly not a sensible thing to do!
select * from aisles,products

• By using a JOIN, I got all combined records with a match between the tables in specified columns. That works very well, but it does not report any "orphan" data - i.e. aisles that have no products in them, or products which are not in any know aisle. That's 3 records in my example
select * from aisles join products on aisles.aid = products.aid

• By using a LEFT JOIN, I got all the combined records of the example above PLUS orphan records in the left hand table - in my example, that's aisles with no products.
select * from aisles left join products on aisles.aid = products.aid

• And finally, by adding where pid is NULL - pid being the primary key in the second table - I'm able to list out only the orphan records. In my example, there's just one of those.
select * from aisles left join products on aisles.aid = products.aid where PID is NULL