Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SQL JOINS

by suresh

This section explains how to write an SQL query to Join two or more tables using the SQL Joins with an example. There are six types of SQL Joins, and they are:

  1. Inner Join: Also called Join. It returns the rows present in both the Left table and right table only if there is a match. Otherwise, it returns zero records.
  2. Full Outer Join: Also called as Full Join. It returns all the rows present in both the Left table and the right table.
  3. Left Outer join: Or called as Left Join. It returns all the rows present in the Left table and matching rows from the right table (if any).
  4. Right Outer Join: Also called as Right Join. It returns matching rows from the left table (if any), and all the rows present in the Right table.
  5. Self Join: It is used to Join the table with itself. We can use this technique to calculate Running Total etc.
  6. Cross Join: It is used to return the Cartesian product of two tables. It means, Number of rows in Employees multiplied by Number of rows in Department table

It is one of the Frequently Asked Question. For this SQL Joins query example, We use the below data

SQL JOINS Example 1

Data present in the Department Table is:

SQL JOINS Example 2

SQL JOIN Example

The visual representation of the SQL Server Inner Join, Full Outer Join, Left Outer Join, Right Outer Join, Self Join, and Cross Join are

SQL JOINS Example 0

SQL Inner Joins Example

This example shows how to write an Inner Join.

-- SQL Server Joins - Inner Join Example
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL JOINS Example 3

SQL Left Join Example

This example shows how to write a Left Outer Join or Left Join. So, refer Left article in the SQL.

--SQL Server JOINS :- Example for SQL LEFT JOIN, or SQL LEFT OUTER JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  LEFT JOIN [Department] AS Dept
  -- LEFT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL JOINS Example 4

SQL Right Joins Example

Let us see how to write a Right Outer Join or Right Join. We had already explained the Right Join.

--SQL Server JOINS :- Example for RIGHT JOIN, or RIGHT OUTER JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT JOIN [Department] AS Dept
  -- RIGHT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL JOINS Example 5

SQL Full Joins Example

This example will show you how to write a Full Outer Join or Full Join.

--SQL Server JOINS :- Example for FULL JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  FULL JOIN [Department] AS Dept
  -- FULL OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
SQL JOINS Example 6

SQL Self Joins Example

In this example, we show how to write a SQL Server Self.

--SQL Server JOINS :- Example for SQL SELF JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,NewEmp.[YearlyIncome] + 25000 AS [New Income]
  FROM [Employees] AS Emp,
	[Employees] AS NewEmp
  WHERE Emp.EmpID = NewEmp.EmpID
SQL JOINS Example 7

SQL Cross Join Statement Example

This example shows how to find a Cartesian Product using Joins or simply Cross Join.

--SQL Server JOINS :- Example for SQL CROSS JOIN 
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
SQL JOINS Example 16

Multiple Joins Example

Data Present in the login table

SQL JOINS Example 8

In this example, we use the SQL Multiple Joins in one SELECT Statement. We use the Inner Join and Left Join on above tables.

--SQL Server JOINS :- Example for Sql Multiple Joins 
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName]
      ,Emp.[LastName]
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
	  ,logg.USERNAME
	  ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  LEFT JOIN [LogInTable] AS logg
  ON Emp.EmpID = logg.LogID
SQL JOINS Example 9

SQL Join Where Clause

You can also use Where Clause along with the Joins Max. We are using where clause to display the Employees whose yearly income is greater than or equal to 60000. For this, we used Inner Join to Join the Employee & Department table. Next, we used Where Clause with a condition

-- SQL Server Joins Example
USE [SQLTEST]
GO
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE YearlyIncome >= 60000
SQL JOINS Example 17

Join Where Clause Example 2

It is another join example to show the Where Clause. Here, we used the Left Join to Join the Emp and Department table. Next, we used Where condition to display the employees whose Education is not Bachelors

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  LEFT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE Education <> 'Bachelors'
SQL JOINS Example 10

SQL Join Where Clause and IS NOT NULL Example

We used the Right Join to Join the Employee and Department table. Next, within the Where clause we used IS NOT NULL to display the records whose ID is not NULL

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  RIGHT OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE EmpID IS NOT NULL
SQL JOINS Example 11

Joins and Where Example 4

Used Full Join to Join tables. Next, within the Where clause

  • we used IS NOT NULL to display the records whose ID is not NULL
  • AND Operator to add another condition
  • Sales > 1000 display employees whose sales is greater than 1000
-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  FULL OUTER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  WHERE EmpID IS NOT NULL AND Sales > 1000
SQL JOINS Example 12

Joins and Where Example 5

We used Cross Join along with Where Clause

-- SQL Server Joins Example
SELECT Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
  WHERE EmpID IS NOT NULL AND Sales > 3000
SQL JOINS Example 13

SQL Joins Order by and Top Clauses

You can also use the Joins along with Top Clause and Order By. In this example, we used the Inner Join to join Employee and Department tables. Next, we used Order By Clause to sort the records in Descending order using yearly income. Finally, Top Clause will select the first 8 records from the query result.

--SQL Server JOINS Example 
SELECT TOP 8 Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  INNER JOIN [Department] AS Dept
  ON Emp.DeptID = Dept.DeptID
  ORDER BY YearlyIncome DESC
SQL JOINS Example 14

This is another Sql Server join example to experience the top and order by along with Joins.

--SQL Server JOINS Example
SELECT TOP 10 Emp.[EmpID]
      ,Emp.[FirstName] + ' ' + Emp.[LastName] AS Name
      ,Emp.[Education]
      ,Emp.[YearlyIncome]
      ,Emp.[Sales]
      ,Dept.[DepartmentName]
  FROM [Employees] AS Emp
  CROSS JOIN [Department] AS Dept
  ORDER BY DepartmentName DESC
SQL JOINS Example 15

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021· All Rights Reserved.
About | Contact | Privacy Policy