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 .
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!
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.
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
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
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 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 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 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.