Useful queries (inner join by example)

Find out the common records from two tables

Sometimes we need to figure out the common records from two tables. Here we figure it out step by step:-

Step 1 - Create two tables as below:


CREATE TABLE tblA(
id INT
,name VARCHAR(20)
)
GO


CREATE TABLE tblB(
id INT
,name VARCHAR(20)
)
GO

Step 2 - Now insert some records into these two table as below:


INSERT INTO tblA VALUES(1, 'Alok')
INSERT INTO tblA VALUES(2, 'Ashu')
INSERT INTO tblA VALUES(3, 'Manu')
INSERT INTO tblA VALUES(4, 'Ravi')
INSERT INTO tblA VALUES(5, 'Baby')
GO


INSERT INTO tblB VALUES(1, 'Alok')
INSERT INTO tblB VALUES(2, 'Ashu')
INSERT INTO tblB VALUES(3, 'Manu')
INSERT INTO tblB VALUES(4, 'Ravi')
INSERT INTO tblB VALUES(6, 'Raju')
GO

Step 3 - Now write the query to find out the common records:


SELECT a1.id, a1.name, a2.id, a2.name
FROM tblA a1
JOIN tblB a2 ON a1.id = a2.id
GO

And the result would be like this


id      name       id     name
1       Alok        1        Alok
2       Ashu        2       Ashu
3       Manu       3       Manu
4       Ravi         4       Ravi



Thanks.

Useful queries (self join by example)

How to find out the Manager of respective employee from a single table (Self Join by example)

Suppose here is a table called employee:-

empId       name           mngrId 
   1            Alok                 2
   2            Ashu                 3
   3            Manu                4
   4            Ravi                  5
   5            Baby                 1

Now the requirement is to find out the manager of each employee. Lets do it step by step:-

Step 1- Create a table with following statement:


CREATE TABLE employee(
empid INT
,name VARCHAR(20)
,mngrId INT
)
GO

Step 2- Insert the data into employee table:


INSERT INTO employee SELECT 1, 'Alok', 2
INSERT INTO employee SELECT 2, 'Ashu', 3
INSERT INTO employee SELECT 3, 'Manu', 4
INSERT INTO employee SELECT 4, 'Ravi', 5
INSERT INTO employee SELECT 5, 'Baby', 1
GO

Step 3- Write the query to fetch the required result:


SELECT e1.name empName, e2.name mngrName
FROM employee e1
      JOIN employee e2 ON e1.mngrId = e2.empid
GO

Thanks.

GET SERVER VERSION

GET SERVER VERSION - 

SELECT @@VERSION VersionInfo
GO