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 :
INNER JOIN
OUTER JOIN
CROSS JOIN
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:
SQL Server Management Studio don’t require Outer keyword.
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.
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy
Privacy Overview
This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
8 Replies to “SQL Joins Explained | SQL Beginner Guide”