Welcome to Appsloveworld, In this session we will understand the different types of joins that are available and SQL server specifically
- Cross join
- Inner join
- Outer join with realtime examples.
Joins in the SQL server are used to retrieve data from two or more related tables. As we know that tables are related to each other using foreign key constraints.
Let’s understand with an example,I have TblStudent table which has got name, gender, and University_Id columns. and then I have the TblUniversity table which has got the UniversityName, location and country_Id. And the last table is the TblCountry table which has got Country_Id and CountryName columns
TblStudent and TblUniversity are related using foreign key University_Id and TblUniversity and TblCountry are related using foreign key country_Id. Now, if you’re not sure what foreign key is, please read this post.
If you want SQL script to create these tables, you can find the script at end of the post.
So these two are related tables. Now, let’s say I ask you to write a query which will give me output as shown in the below image.
I want the name of the student, gender, and the University name. And if you look at the output columns that I require, the first 2 columns name, gender, they are present in the TblStudent, whereas the University name is present in the TblUniversity table.
So all these columns are not coming from a single table. They are spread across two different tables. So obviously, if I have to retrieve output, then I will have to join these two tables.
To join these two tables in SQL server, we have got different types of joins.
For example, we have inner join, outer join and Cross joins and again the outer joins in the SQL server are divided into three different categories i.e Left outer join, right outer join, full outer join or you can just say left, join, right join, full join.
What is inner join?
Now let’s talk about inner join. Now let’s say, I want output like below image, I want the name of the student, the gender, and the University name.
So let’s see how we do this using an inner join. If you have two tables, in the inner join the matching rows between the two tables are retrieved.
For example, if you look at the TblStudent table here, everybody has University_Id except Jesiah and Rocky.
If you look at Jesiah and Rocky’s record, the University_Id is Null, which means these rows don’t have a matching University_Id from the TblUniversity table.
So when I join these two tables using inner join, only the matching rows between these two tables are retrieved.
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Inner JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
or
SELECT StudentName, Gender, UniversityName
FROM TblStudent
JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
And if you look at the output, I don’t get Jesiah and Rocky’s records.Why? Because there University_Id does not match with the University_Id column in the TblUniversity table.
So inner join will only give you matching rows between both the tables involved in a join.
Query Explanation:
Now let’s see how to write the inner join query itself. So if you look at the two tables that we have,
In the TblStudent table, we have got 8 rows and in the TblUniversity table, we have got 7 rows. And what do we want? We want the name, gender and University name columns in the output From team TblStudent.
The first 2 columns are present in the TblStudent table. But the University name column is present in the TblUniversity table. So I will have to join with that table and to join the table. You use the join keyword.
So we are saying I want to join the TblStudent table with the TblUniversity table.
Now if you want to join these two tables, how do you want to join these two tables? What’s the common column between them? The common column is the University_Id column and to specify the join condition you use the on clause.
We have the University_Id column, so use that to look up the TblUniversity table and the University_Id column within that table.so it’s pretty simple.
SQL Inner join with more than two tables
Now we want the country name also in the result, then we also need to join the TblCountry.
SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent
Inner JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
INNER JOIN TblCountry
ON TblUniversity.Country_Id=TblCountry.Country_Id
What is left join?
Now let’s say my requirement is in such a way that I not only want the matching rows, I want the non-match rows as well from the TblStudent.
Basically, I’m saying I want all the students, irrespective of whether they are assigned to a University or not. I want all the Students.
So how do we do that? indirectly you are saying, I want the matching rows, plus the non-matching rows from the TblStudent stable, which means you want everything from the left table matching and non-matching.
if you look at the left join, retains all the matching rows plus non-matching rows from the left table. You know, everything remains exactly similar except that instead of the inner join, you will say left join.
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Left Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
or
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Left OUTER Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
Now excute above query,and see how many records we get. So if you look at the output now, we should have got both Jesiah and Rocky’s records as well.
But if you look at the UniversityName Colum, it’s basically NULL and it makes sense why? Because they don’t have a University_Id they are not assigned a University yet.
So that’s why the University name will be NULL. so left to join basically returns all the matching rows between both the tables, plus non-matching from the left table.
And the picture depicts exactly that scenario. And when you use a left join you can say just left join or you can say left outer join, the query results will not be affected. So the outer keyword is optional there.
SQL Left join with more than two tables
SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent
Left JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
Left JOIN TblCountry
ON TblUniversity.Country_Id=TblCountry.Country_Id
What is Right join?
if you look at the two tables in the below image, you have a UniversityName called Massachusetts Institute of Technology (MIT) with University_Id = 1. As you can see no Student in the TblStudent table belongs to this University.
If there is a requirement, if somebody asks you, I want all the matching records between both the tables, plus, non-match records from the right table.
so from the TblUniversity table, I want the Massachusetts Institute of Technology (MIT) record as well, irrespective of whether there is a student assigned to that University or not. we want all the rows from the right table.
Let’s see what happens. if we convert this left outer join to right outer join.
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Right OUTER Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
or
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Right Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
when we execute this we should get around 7 records. Look at the above image with all the matching rows, we’ve also got and Massachusetts Institute of Technology (MIT) rows.
And if you look at the name, gender columns is NULL, because no student is assigned to this University, so obviously you will not have any name, gender.
so whenever you want to return all the matching rows between both the tables that are involved in a join plus non-matching rows from the right table, that’s when we go for right outer join.
SQL Right join with more than two tables
SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent
Right JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
Right JOIN TblCountry
ON TblUniversity.Country_Id=TblCountry.Country_Id
What is Full JOIN?
So you might have guessed by now if I want all the matching rows between both the tables, plus non-matching rows from the left table and non-matching from the right table,
then what type of join do I use? full outer join gives me exactly that result.
Full-outer join will give you all the records from both the left and right tables, including the non-matching rows.
How do we write a full outer join query? Just specify full outer join.
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Full Join TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
so obviously when we execute this query, we should get around 9 records, 6 matching records two non-matching from left, and one non-matching from the right table.
so far we have seen inner join, left join and right join and outer join, full outer join but apart from all these types of joint, we also have another join called Cross Join.
SQL FULL join with more than two tables
SELECT StudentName, Gender, UniversityName,Location,CountryName FROM TblStudent
Full JOIN TblUniversity
ON TblStudent.University_Id = TblUniversity.University_Id
Full JOIN TblCountry
ON TblUniversity.Country_Id=TblCountry.Country_Id
What is Cross Join?
So what a cross join do and how do I write that query? First, let’s write the query, we look at that output and then we’ll decide what cross join is going to do.
So if it’s a cross join, instead of saying full outer join, all I say is cross join. Now, remember, a cross join will not have an on clause.I’ll tell you in a bit why a cross join shouldn’t have an on clause.
SELECT StudentName, Gender, UniversityName
FROM TblStudent
Cross Join TblUniversity
when I execute this query, look at this, look at the number of rows that we are getting 56 rows
And if you remember, how many rows are there in these tables, 8 rows in the TblStudent table, and 7 rows in the TblUniversity table.
when we execute the cross join query, we are getting 56 rows. So basically what’s happening? The number of rows in the TblStudent table is being multiplied by the number of rows in the TblUniversity table. So across join will give us the Cartesian product of the tables that are involved in the join.
So in summary, cross join returns, the Cartesian products of the tables involved in the join, whereas inner join returns only the matching rows between the tables.
I look at below picture, which will make the understanding of join pretty much easy. so if you look at the inner join, only matching rows, left join matching rows plus non-matching rows from the left table.
Right join matching rows, plus non-matching from the right table, but as full join matching rows between both the tables and non-match matching from the left and non-matching from the right.
Table Script
GO
/****** Object: Table [dbo].[TblCountry] Script Date: 09/11/2020 1:40:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblCountry](
[Country_Id] [int] NOT NULL,
[CountryName] [nvarchar](max) NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[Country_Id] ASC
)
)
GO
/****** Object: Table [dbo].[TblStudent] Script Date: 09/11/2020 1:40:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblStudent](
[Id] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[University_Id] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
GO
/****** Object: Table [dbo].[TblUniversity] Script Date: 09/11/2020 1:40:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUniversity](
[University_Id] [int] IDENTITY(1,1) NOT NULL,
[UniversityName] [nvarchar](max) NULL,
[Location] [nvarchar](max) NULL,
[Country_Id] [int] NULL,
CONSTRAINT [PK_University] PRIMARY KEY CLUSTERED
(
[University_Id] ASC
)
)
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (1, 'USA')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (2, 'United Kingdom')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (3, 'China')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (4, 'India')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (5, 'Australia')
GO
INSERT [dbo].[TblCountry] ([Country_Id], [CountryName]) VALUES (6, 'Japan')
GO
SET IDENTITY_INSERT [dbo].[TblStudent] ON
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (1, 'Jesiah', 'Male', NULL)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (2, 'Evelyn', 'Female', 2)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (3, 'Advika', 'Female', 4)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (4, 'Bowie', 'Male', 3)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (5, 'Adweta', 'Female', 6)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (6, 'Alaric', 'Male', 5)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (7, 'Aarna', 'Female', 7)
GO
INSERT [dbo].[TblStudent] ([Id], [StudentName], [Gender], [University_Id]) VALUES (8, 'Rocky', 'Male', NULL)
GO
SET IDENTITY_INSERT [dbo].[TblStudent] OFF
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] ON
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (1, 'Massachusetts Institute of Technology (MIT)', '77 Massachusetts Ave, Cambridge, MA 02139', 1)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (2, 'Stanford University', ' Stanford University Stanford, CA', 1)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (3, 'University of Delhi', 'Benito Juarez Marg, South Campus, South Moti Bagh, New Delhi,', 4)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (4, 'The Australian National University', 'Canberra ACT 0200, Australia', 5)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (5, 'Tsinghua University', '30 Shuangqing Rd, Haidian District, Beijing', 3)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (6, 'University of Tokyo', '7 Chome-3-1 Hongo, Bunkyo City, Tokyo 113-8654, Japan', 6)
GO
INSERT [dbo].[TblUniversity] ([University_Id], [UniversityName], [Location], [Country_Id]) VALUES (7, 'University of Oxford', ' Oxford OX1 2JD, United Kingdom', 2)
GO
SET IDENTITY_INSERT [dbo].[TblUniversity] OFF
GO
ALTER TABLE [dbo].[TblStudent] WITH CHECK ADD CONSTRAINT [FK_TblStudent_University] FOREIGN KEY([University_Id])
REFERENCES [dbo].[TblUniversity] ([University_Id])
GO
ALTER TABLE [dbo].[TblStudent] CHECK CONSTRAINT [FK_TblStudent_University]
GO
ALTER TABLE [dbo].[TblUniversity] WITH CHECK ADD CONSTRAINT [FK_TblUniversity_Country] FOREIGN KEY([Country_Id])
REFERENCES [dbo].[TblCountry] ([Country_Id])
GO
ALTER TABLE [dbo].[TblUniversity] CHECK CONSTRAINT [FK_TblUniversity_Country]
GO
That’s it for today. Thank you for listening. Have a great day.
The post What is Sql Joins? With Realtime Examples appeared first on Software Development | Programming Tutorials.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- SQL conditional variables in select query
- Printing source code from sql server 2005 stored procedures
- Handling BLOBs in Entity Framework 4.0 in a stream-fashion
- Roll up count in hierarchical table(self referential table)
- Foreach Loop container in SSIS suggestion
- Integer out parameter null issue in ADO.NET while calling through procedure
- Multiple selection from SQL Table
- SQL Return every row even if Null values
- How can I retrieve a specific text node (first, or second, or last) from my xml when my xml has mulitple text nodes using SQL Server 2017?
- SQL Select where first value and last value of daterange
- Problems in Executing the SP which has while(true) loop in SQL SERVER
- SQL while loop don't update table
- Querying a smalldatetime's date and time separately in SQL server?
- Convert ID to Int and save to datatable
- How to decode HTML content from database in ASP.Net Page
- ROW_Count in VIEW
- How to Download database from hosting server plesk-web13
- How do you check if a record exists in SQL server 2012 when NULL values are possible?
- insert with order by is faster
- Trying to accomplish without dynamic SQL (sql server)
- SQL Server : how to join this?
- SqlDataAdapter.Fill(DataTable) not filling DataTable
- auto generating surrogates shows an error to many rows to insert
- T-SQL - Get runduration from a restore job
- SQL divide number back and forth results in rounding error
- SQL - Left Join problem
- Selecting multiple values into single row - SQL server
- SQL remove characters that aren't in a regex pattern
- Calculating Percentages with SUM and Group by
- Taking the Largest SUM from a table
- SELECT SQL Variable - should i avoid using this syntax and always use SET?
- How can I make this query recursive Sql Server?
- Execution Plan behaviour with DateTimeOffset
- Can I create a dacpac including data in SQL Server 2008?
- SQL Server Database synchronization with my project in c#
- Apache Camel 3.x + Spring Boot + JDBC DataSource: java.lang.IllegalStateException: No supported DataSource type found
- SQL Server instance discovery using SqlServer PowerShell module
- How do I retrieve a set number of characters that precede and follow a key word?
- SQL Server error on insert some GUIDs
- In Microsoft SQL Server is there a better way to fill null values in both directions than what I am currently doing?
- How to create type from existing table in SQL Server?
- Wild card search in SQL
- Counting active items over time periods based on activity start and duration
- SQL Server convert row values to columns
- Problems Iterating through a list of SQL tables with python
- How to select based on custom column
- Trigger to compare date SQL Server
- Unable to see database diagram option in SQL Server
- accept phone numbers of specific characters
- How to monitor and alert a table row value in SQL Server for a maximum value
- How do I pass multiple values to parameter?
- Column name of function in SQL Server
- Inserting rows in a table from another table using a third table
- The wait operation timed out
- Migrate XML data to Microsoft SQL Server 2016
- Working with stored procedure
- Outer join multiple tables with flattened results
- Using COUNT() in WHERE Clause
- infinspan for SQL server
- SQL Select from 2 tables with dominance
- Make SQL Server faster at manipulating data - turn off transaction logging?
- Issue with SELECT TOP 1
- How many rows were updated by a stored procedure
- Find matched column records in one table that may be in multiple columns in a second table
- SQL - Adding/Disabling a Where clause based on a boolean condition in Select Statement (ASP.net SQLDataSource)
- How to select rows from a group using a condition
- Does ADO.net return results after the specified stored procedure call only or does it wait until all triggers are executed?
- How can i optimize the query to get values based on each ID?
- .xmla deployment to SQL using C#
- How to resolve "MSOLAP.4 Provider is not registered on the local machine" error without install SQL in local machine?
- TADOQuery Join tables - Insert \ Delete records from result
- Microsoft Codename "Data Transfer" identity column error
- Bad Dates: SQL Server Offers ISDATE(), But Informix Does Not. Is There an Equal Workaround?
- SQL Server CASE statement with two conditions
- Converting date time format
- print to PDF using xp_cmdshell
- Join 2 tables in SQL Server
- Entity Framework - how to manage tables in different databases but on the same server?
- Delphi XE7 MultiDevice SQL Error TIMESTAMP Ret. Field
- Is there a way to expose SQL Server relational schema as REST API?
- How to calculate the date difference between rows with special first row rule
- multi-part identifier could not be bound on insert from another database on another server
- How to call methods on a variable in SQL Server
- SQL Server Create multiple foreign keys
- SQL Server - parse GPX file
- SQL query to get three most recent records by customer
- sqlcmd works in CMD and not in bash
- SQL Server CDC on premise to AWS
- How to split the column by identifier (|) and get the first item and group by?
- Azure timer Function output binding record set in a queue
- Composite primary key with foreign keys out to different tables
- Raising numbers to fractional powers in SQL
- T-Sql Multiple Criteria On Where Clause
- Limit of arguments on Coalesce SQL function?
- SQL Server Change Data Capture - Operation Type 3 shows NULLS in fields that aren't NULL
- Parse Time only (Minutes:Seconds.Ms) for SQL Server compliant C#
- SQL Server Trigger to send email on insert with conditions and using table values
- The multi-part identifier could not be bound in ms sql server database
- Hibernate Lock table
- how to count returning zero? ms sql
- SQL - Copy multiple rows for each row from another query
- Query SQL with like operator from two tables
- Get specific records from DB
- Is it better to update or increment a value when persisting to a database?
- Proper rename table in sql server
- Generate a random number which is not there in a table in sql server
- Select only few columns from procedure and insert into table
- Error : [Microsoft][ODBC SQL Server Driverr][SQL Server]Insert Error: Column name or number of supplied values does not match table definition
- Delete specific word at the beginning
- How to support both PostgreSQL and SQL Server in a Node.JS application?
- Most efficient way to view data in table in SQL Server?
- Convert Every Other Row into columns
- Can SQL Server CONVERT null values from JAVA to 0?
- Conditionally setting visibility-toggling in SSRS
- SQL Query - Select numbers where last 7 digits are identical
- SQL Joining Tables Gives Wrong Results
- SQL Server Reporting wrap output rows
- How to set variables in While Loop in Sql Server
- Inserting a Row in a Table from Select Query
- How to INSERT INTO table from SELECT without specifying columns?
- Can a stateless WCF service benefit from built-in database connection pooling?
- EF Core Migrations: Drop Unique Constraint when it exists
- Limit data by user supplied date range
- SQL Using subquery in where clause and use values in select
- Entity Framework not loading child entities correctly
- SQL how to concat table Valued Parameter to Values in an existing table on ID
- Incrementing a counter based on a partition
- Deleting a SQL row ignoring all foreign keys and constraints
- Failure to generate user instance of SQL Server error, when setup application is run on another computer
- how to add last values of same group in sql query
- MSSQL Linked Server error: The OLE DB provider "OraOLEDB.Oracle" for linked server supplied inconsistent metadata for a column
- SSIS hangs at Pre-Execute - 50 percent complete sql command
- Pivot table without aggregation and multiple pivot columns
- Storing article-length blocks of text and markup in SQL Server
- Transaction Replication and Triggers
- SQL Server: store INT in BIGINT column can be a problem?
- Executing remote stored procedure within sp_executesql
- SQL Server - Convert VarChar to Date in ALTER TABLE
- Finding the database name from tsql script
- SQL Server nullable values storage impact
- How to use temp variable to replace specific path in xp_fileexist function
- Thread inside a Loop? SQL Server
- Select matching rows as columns
- Multiple DATEADD functions in one query - TSQL
- HAVING MAX(Value) is not working in SQL Server query
- Trying to find duplicate values in TWO rows and TWO columns - SQL Server
- Query for Min, Max, Avg, and Last Value in TSQL
- Week() function in sql script
- Pivot Tables In Sql Server With Spaces In Cell
- Sql Server 2008 - Convert localized DateTime column to UTC