Udemy logo

sqljoinexampleThere’s an old joke. A SQL query walks into a bar and sees two tables. He walks over and asks if he can  join them…. The joke comes from the “if you didn’t laugh, you’d cry” school of programing. For some people the lesson using a SQL join example is the point when they give up on database programming and learning SQL, and go find a bar instead!

SQL joins can be a bit tricky. But please don’t quit – once you’ve had the “gotcha” moment you’ll wonder what all the fuss was about! First though, if you are new, or rusty on basic SQL – check out this Introduction to SQL .

You’ve Convinced Me – What’s A Join?

The whole point of a relational database is to group data together in tables in a manner that eliminates duplication.  It’s efficient. If you decide that the Personnel Department is now called Human Resources – it’s a good thing that you only have to change the name once in the department table, rather than 300 times in the personnel table, once for each employee of the department.

So, when designing a relational database you want to separate dissimilar data.  We  keep employees with skills in one table, we keep job assignments requiring skills in another. Well in real life it would probably be a whole lot more complex than that, but stay with me here, I’m talking big picture. If you want to learn the details check out an Introduction to SQL Database for Beginners course.

However, as soon as you have designed a beautifully efficient or normalised database, along will come a pesky user with an annoying query. And the queries or reports will invariably require data from more than one table.

In our example, the boss wants to know which of her employees have skills matching client’s job requests.  Does this mean you need to re-design your database? Should you download everything into a spreadsheet and make up a new table there? Nooooo!

What you need is a join. A join is a way to query across tables and use the resulting temporary table to report results.

SQL Join Example #1 The Inner Join

The inner join is sometimes called the simple join. Think of it this way: on the left you have a bunch of employees who have some skills, on the right you have some jobs that require specific skills which need assignment to an employee. You check the overlap between staff member’s skills and the job’s requirements, and that’s the report the boss wants.

If the question asked is “Which  employees have the skills to undertake these assignments?” – the answer is an inner join.

We’ll use a couple of absurdly simple data tables in the SQL join examples.

Staff

name hasSkill
Mary web design
Jose PHP
Bob SQL

Job

JobID skillReq
J001 copywriting
J002 SQL
J003 PHP

 

An inner join of:

SELECT staff.name, staff.hasSkill, job.jobID FROM staff INNER JOIN job ON staff.hasSkill = job.skillReq;

Would return:

name hasSkill jobID
Jose PHP J003
Bob SQL J002

 

SQL Join Example #2 The Outer Join

This is a bit more inclusive – there are several versions of the outer join: full, left and right.

For our purposes lets keep our people to the left and the client’s jobs on the right.

Full Outer Join Example

The full outer join is very inclusive, a warm and politically correct, sort of SQL join. The result of a full outer join in our example is a long list of every person in the office, matching any job that required their skills, plus those employees with skills that don’t match any jobs (woops time to upskill!), plus jobs that require skills that no one has (time to hire!). Not every row will be complete,  there will blanks (nulls) for the columns where there was no match in the other table.

SELECT staff.name, staff.hasSkill, job.skillReq, job.jobID FROM staff FULL OUTER JOIN job ON staff.hasSkill = job.skillReq;
name hasSkill skillReq jobID
Mary web design <null> <null>
Jose PHP PHP J003
Bob SQL SQL J002
<null> <null> copywriting J001

 

Left Outer Join Example

Or we might be a worker-friendly workplace – and implement a left outer join . This would include everyone in the office, even if their skills were not required, plus the jobs that required skills that were covered by the people available. So our left joins are a bit socialist! They include all the employees, but only include the jobs that require skills employees have.

SELECT staff.name, staff.hasSkill, job.jobID FROM staff LEFT OUTER JOIN job ON staff.hasSkill = job.skillReq;
name hasSkill jobID
Mary web design <null>
Jose PHP J003
Bob SQL J002

 

Right Outer Join Example

If we were a little more worried about the bottom line maybe we’d use a right outer join. This time we’d only list those employees who had skills required by a job, plus any jobs that had skills requirements we couldn’t match to an employee. Now the focus is to the right and a bit fiscally conservative!

SELECT staff.name, job.skillReq, job.jobID FROM staff RIGHT OUTER JOIN job ON staff.hasSkill = job.skillReq;
name skillReq jobID
Jose PHP J003
Bob SQL J002
<null> copywriting J001

SQL Join Example #3 The Self Join

This is slightly inward looking join. But if you think about our keen workers and consider that some of them may be supervisors. A self join might be useful to identify all people who have the skills to undertake the job PLUS are supervisors.  Or maybe we have some employees in Australia and others in New York. We’d need a self join to establish which of our skilled SQL programmers lived in Australia.

Why Should You Care About SQL Joins

If you are not confident using joins, you certainly aren’t the only one. Plenty of programmers attempt to avoid joins and look for alternatives. One of the most popular alternative is the nested select statement. This is lazy programming. This will become obvious when your tables fill up with data, requiring the machine to make a recursive query on a query, it’s slow, real slow. Replace the query with a join and the database performance will improve significantly.

What Understanding SQL Joins  Can Do For You

Want to add a sophisticated search form to your website? You need to understand SQL joins.

Want to learn an industry standard database language used by many large corporations, who employ programmers you need to know Oracle and their version of SQL.

Plus, once you’ve mastered SQL joins – you’ll have earned that trip to the bar!

Page Last Updated: October 2013

Top courses in SQL

Complete SQL and Databases Bootcamp: Zero to Mastery [2023]
Andrei Neagoie, Mo Binni, Zero To Mastery
4.6 (3,696)
SQL and PostgreSQL: The Complete Developer's Guide
Stephen Grider
4.7 (5,422)
Highest Rated
SQL - The Complete Developer's Guide (MySQL, PostgreSQL)
Academind by Maximilian Schwarzmüller, Maximilian Schwarzmüller, Manuel Lorenz
4.7 (1,023)
101 Practice SQL Questions: Basic to Advanced
Curtis Norman
4.9 (18)
Highest Rated
The Complete SQL Bootcamp 2022: Go from Zero to Hero
Jose Portilla
4.7 (154,970)
Bestseller
The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert
Colt Steele, Ian Schoonover
4.6 (75,316)
Bestseller
SQL Programming Basics
Global Academy
4.5 (3,837)
SQL Server 2022 : The 28 hour Masterclass course (16 in 1)
ELOA FORMATION | SQL | Postgres | Powershell| MySQL | Python | TSQL Administration,TSQL, and SQL Server performance.
4.5 (185)

More SQL Courses

SQL students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy Business.

Request a demo