Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases Querying Relational Databases Joining Table Data with SQL Inner Joins

Posted by nb11
nb11
17,552 Points

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
229,732 Points
Steven Parker
Steven Parker
229,732 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

Erich Martin
Erich Martin
5,141 Points

Great cheatsheet!

Teacher Russell
Teacher Russell
16,873 Points
Teacher Russell
Teacher Russell
16,873 Points

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

nb11
nb11
17,552 Points

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

nb11
nb11
17,552 Points
nb11
nb11
17,552 Points

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
5,337 Points
Ikechukwu Arum
Ikechukwu Arum
5,337 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;
Enock Addey
PLUS
Enock Addey
Courses Plus Student 10,420 Points
Enock Addey
Enock Addey
Courses Plus Student 10,420 Points

Thank you, Steven Parker!