Databases Querying Relational Databases Joining Table Data with SQL Inner Joins

Joins??

HI,

I'm trying to figure out how to create a join that shows the remaining items not in the join from both tables in the last part of Querying Relational Databases.

The questions is: Use a JOIN to find out which patrons haven't had any loans. Select their first name and email address.

The databases are loans and patrons and here is my code:

SELECT DISTINCT first_name, email
  FROM patrons
  JOIN loans ON loans.patron_id = patrons.id;

It is bringing up the patrons which have had loans but I can't figure out how to bring up the ones who have not had loans, there is one I believe?

4 Answers

Steven Parker
Steven Parker
186,662 Points

:point_right: You need an OUTER join to get the records that don't match.

The standard "join" is an inner join. This will only return records that have common values.

If you want to see records that do not have matching values, you need an outer join, further filtered by a WHERE clause for the null match. For example:

SELECT DISTINCT first_name, email FROM patrons
    LEFT OUTER JOIN loans ON loans.patron_id = patrons.id
    WHERE loans.patron_id IS NULL;

:information_source: Here's a handy "cheat sheet" from a Code Project article for the 7 kinds of joins:

(this one is second down on the left) SQL Joins

Great cheatsheet!

Teacher Russell
Teacher Russell
16,868 Points

What is the significance of the double letters, Table A A, Table B B?

It's a short hand way of renaming the table for your own query, making it shorter to write.

It could have been written as 'TableA AS A' or 'TableB AS B'

So when you reference the tables in your query you just write 'A' instead of 'TableA'.

Helps alot when you have long table names

Ah that's great thank you for your help.

I can see the JOIN I was using was the wrong one and needed the WHERE clause also. This is my code that gave me what I was looking for:

SELECT first_name, email
  FROM patrons
  LEFT JOIN loans ON loans.patron_id = patrons.id
  WHERE loans.loaned_on IS NULL;
Ikechukwu Arum
Ikechukwu Arum
3,495 Points
SELECT first_name, email, patrons.id FROM patrons LEFT JOIN loans ON patrons.id =loans.patron_id WHERE patrons.id IS NOT loans.patron_id;