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.