-
SQL Installing SSMS
-
SQL Syntax
-
SQL Datatype
-
SQL Database
-
SQL Table
-
SQL Select
-
SQL Distinct
-
SQL Count
-
SQL Top
-
SQL Aliases
-
SQL In
-
SQL Insert
-
SQL Update
-
SQL Null
-
SQL Like
-
SQL Wildcards
-
SQL Nested Query
-
SQL Between
-
SQL Unique
-
SQL Primary Key
-
SQL Foreign Key
-
SQL Check
-
SQL Default
-
SQL Database Diagram
-
SQL Stored Procedures
-
SQL Joins
-
SQL Inner Join
-
SQL Left Join
-
SQL Right Join
-
SQL Full Join
-
SQL Trigger
SQL Inner Join
INNER JOIN in SQL is the most common and important type of join which allows users to access matching data from two or more tables. When the join condition is met between the tables, then it returns all the common rows from them.
The Venn diagram of INNER JOIN is shown in the following picture. The shaded region of the Venn diagram shows the intersection values of two tables:
Example:
SELECT Column_Name1, Column_Name2, ….., Column_NameN
FROM Table_Name1 INNER JOIN Table_Name2
ON Table_Name1.Column_Name = Table_Name2.Column_Name;
Let’s take two tables named Employee_Details and Department to understand the concept of INNER JOIN. The Employee_Details table contains Emp_ID, Emp_Name, Dept_ID, and Emp_Salary columns. The Department table contains Dept_Id and Dept_Name columns.
We can check the data of Employee_Details and Department table using the following two different queries:
SELECT * FROM Department;
Output:
SELECT * FROM Employee_Details
Output:
The following query joins these above two tables using INNER JOIN in structured Query Language:
SELECT
Employee_Details.Emp_Id,
Employee_Details.Emp_Name,
Department.Dept_Name,
Employee_Details.Emp_Salary,
FROM Department INNER JOIN Employee_Details
ON
Department.Dept_ID = Employee_Details.Dept_ID;
Explanation of above INNER JOIN query:
This query joins the Employee_Details and Department table and accesses the records from both the tables where Department.Dept_Id = Employee_Details.DeptId.
It only fetches the details of those employees from both the tables whose Dept_Id in the Employee table matches with the Dept_Id of the Department table.
If the Dept_Id is NULL or does not match, then that row will not show in the output.
Output:
Course Video
NOTE: Practice below questions on site editor.
1. Write a SQL query to get employeeID, Job title, firstName, MiddleName, LastName and title from Person and HumanResources.Employee tables.
NOTE: OUTPUT on SQL Editor Count: 100 Rows.
Output
2. Write a SQL query to get employeeID, Job title, firstName, LastName and startDate from Person, HumanResources.Employee and HumanResources.EmployeeDepartmentHistory tables.
NOTE: OUTPUT on SQL Editor Count: 100 Rows.
OUTPUT
3. Write a SQL query to get employeeID, Job title, firstName, LastName, departmentName, department groupName, startDate from Person, HumanResources.Employee, HumanResources.EmployeeDepartmentHistory and HumanResources.Department tables.
NOTE: OUTPUT on SQL Editor Count: 100 Rows.
4. Write a SQL query to get customerID, storeId, StoreName, SalesPersonID, salesQuota, bonus from Customer, Sales.Store and Sales.SalesPerson Tables.
NOTE: OUTPUT on SQL Editor Count: 4 Rows.
OUTPUT
5. Write a SQL query to join SALES.CUSTOMER, SALES.STORE, SALES.SALESPERSON AND SALES.SALESTERRITORY TABLES to retrieve two columns from each table.
NOTE: OUTPUT on SQL Editor Count: 4 Rows.
OUTPUT
NOTE: Use NORTHWIND DATABASE For Practicing these Questions.
6. Retrieve employeeId, firstname, lastname and TerritoryID for Employees Table where firstName is starts with M and ends with T, joining the EmployeeTerritory Table. [OUTPUT: 3 ROWS]
OUTPUT
OUTPUT
7. Retrieve the Customer ID, Company Name, Contact Name, Order ID, Order Date, Employee ID, and Title from the Customers, Orders, and Employees tables, filtering for orders placed within the temporal span of 1996 and 1997, ensuring that the results are sorted in descending order based on the order date. [OUTPUT: 678 ROWS]
OUTPUT
8.Retrieve the Order ID, Quantity, Product ID, Product Name, Category ID, and Category Name from the OrderDetails, Products, and Categories tables, focusing on product categories such as ‘daily products’, ‘beverages’, and ‘seafood’. [OUTPUT: 734 ROWS]
OUTPUT
9. Retrieve the Product ID, Product Name, Order ID, Unit Price, Quantity, Category Name, Supplier ID, Contact Title, and City from the Products, OrderDetails, Categories, and Suppliers tables, focusing on orderdetails where the quantity ordered exceeds the average quantity, with the additional constraint that the supplier’s city is Tokyo. [OUTPUT: 15 ROWS]
OUTPUT
10. Compose a SQL query to perform a join operation involving the Shippers, Orders, Employees and Customers tables, extracting two columns from each table. Further add filtering for employees who have handled more than 50 orders. OUTPUT: 510 ROWS]
OUTPUT
YouTube Reference:
1) SQL Inner Join in Hindi/Urdu
2) SQL Inner Join in English
