SQL Join Example – Or How To Manipulate Data Like a Pro

There’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

Job

An inner join of:

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

Would return:

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;

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;

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;

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!