SQL Joins Explained | SQL Beginner Guide

SQL Joins is an SQL clause and it combines data from multiple tables on basis of related columns between them. So you have designed a wonderful DBMS & you have created an SQL database ( If Not? Use our sample database) to support your DBMS . You created an ERD & normalized it as well & now you are successfully able to store the transactions into your database. Great Job! But now client has asked you to show you data which you have placed in three four different tables. What Now? How do you do it? SQL Joins is the answer you’re looking for.
More often than not , you will have to create reports & find data which is spread across multiple tables . Primary keys , foreign keys and other candidate keys that we have in our tables are used to join different tables and match the data that needs to be related.
I am a big fan of SQL , reason is the fact that most of the keywords it uses have obvious meanings & easy to understand. Meaning of the word Join is ‘Connect/Link’ & this is exactly what SQL Joins do .

SQL JOINS

We have following types of SQL Joins :

1. INNER JOIN
2. OUTER JOIN
3. CROSS JOIN
4. FULL OUTER JOIN

Mostly , you  will use the first two kind of joins because they are most suitable to find data . The last two are rarely used but for the sake of understanding we will discuss each one!

JOIN SYNTAX

SELECT columns
FROM table1 AS [alias for table1]
JOINNAME table2 AS [alias for table2]
ON[alias for table1].keycolumn=[alias for table2].keycolumn

Take a long breath & have a good at look at this syntax because this is the syntax that all joins follow.

INNER JOIN

Gives back data based on matching conditions. Rows returned should have matching columns present on both sides of the join.
Using our sample database , I want to see about the grades of Students in their courses with their names. We know that  table Person have student names and table StudentGrade has their grades. What should we do ? Take a look at the data & the Select query

SQL Tips:

Writing SQL Joins mainly involves identifying the matching column in both tables . Matching Columns should have same SQL datatype . Names can be different but SQL datatypes should be same . Can you identify the column for matching by looking at the data? Comment below ! If you can
SQL Server Management Studio by default , picks inner join for execution if you fail to mention the complete join name. See below
Let’s apply the following query and see results

SELECT Student.PersonID As StudentID ,
Student.FirstName,
Student.LastName ,
Course.Title ,
Grade.Grade
FROM [School].[dbo].[Person] AS Student
INNER JOIN StudentGrade AS Grade
ON Student.PersonID=Grade.StudentID
JOIN Course AS Course
ON Course.CourseID =Grade.CourseID

Result

Take  a look and see how many courses are in our final result and how many missed. This is our SQL Inner Join with example.

OUTER JOIN

Outer Join returns all data for one table & matching data from other table no matter matching columns match in both tables or not .  They are of two types and you will be able to understand them better by reading about them.

LEFT OUTER JOIN

SQL at it’s best , just as i said this language is so easy because it’s just so natural. Left Outer join returns all data from left side of the query & only matching from right side.

SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course
LEFT OUTER JOIN StudentGrade AS Grade
ON Course.CourseID =Grade.CourseID

When you execute this query , you will find a course ‘Trigonometry ‘ which has no Student but still it appears in our result? Reason is because in this query Course table is on left side so everything on left side will be part of the result. Simple enough right! ?

RIGHT OUTER JOIN

Right Outer join returns all data from right side of the query & only matching from left side.

SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course
RIGHT OUTER JOIN StudentGrade AS Grade
ON Course.CourseID =Grade.CourseID

FULL OUTER JOIN

This join is very ideal when you just want the data of two tables at one place , without worrying about matching columns . It just gives everything from both tables.

SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course
FULL JOIN StudentGrade AS Grade
ON Course.CourseID =Grade.CourseID

Test it at your side and post the images in comments!

SQL Tips:

1.  SQL Server Management Studio don’t require Outer keyword.
2. If someone asks the minimum & maximum number of rows produced in left outer join. What will be your response? If you say that minimum & maximum number of rows returned as a result will be the total number of rows on left side table. You’re partially correct. That will be the minimum number of rows & yeah about maximum . Ehhh!   here’s the thing you can never be sure about maximum. We will discuss this in our upcoming e-book  of SQL Tips & Design Practices which will be exclusively sent to all our subscribers.

CROSS JOIN

Cross Joins produces the cartesian product of the two tables. For instance if  StudentGrade has 40 rows and Course table has 10 rows . When you will cross join them the resultant will be 400 rows.

SELECT Course.Title ,Grade.StudentID,
Grade.Grade
FROM Course AS Course
CROSS JOIN StudentGrade AS Grade

ON keyword and matching columns are not required. Run this query in your SQL Server management studio and see the results.
There’s one more Join called Self Join. We will discuss it in our Advance SQL Concept series because if you’re a beginner i don’t want to mess up with your head. So for now just familiarise yourself with these SQL  joins and go through this SQL Joins Tutorials. Hopefully , it will help you!
I hope this was helpful . Do share this with your friends to help them & Subscribe to get that e-book free . See you with the next article.

Cheers!