SQL SERVER – Get Common Records From Two Tables Without Using Join

I really enjoy answering questions which I receive from either comments or Email. My passion is shared by SQL Server Expert Imran Mohammed. He frequently SQL community members by answering their questions frequently and promptly.

Sachin Asked:

Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,

1. Select column1,column2 From Table1
2. Select column1 From Table2

I want to find common records from these tables, but i don’t want to use Join clause bcoz for that i need to specify the column name for Join condition. Will you help me to get common records without using Join condition? I am using SQL Server 2005.

Imran Mohammed Replied:

If you are using SQL Server 2005, then you can use Intersect Key word, which gives you common records.

SELECT column1
FROM table1
FROM table2

If you want in the output both column1 and column2 from table1 which has common columns1 in both tables.

SELECT column1, column2
FROM table1
WHERE column1 IN
SELECT column1
FROM table1
FROM table2

To do this, make sure your column1 is unique and do not have duplicate records.

This is good answer. INTERSECT is new operator in SQL Server which gives you similar answer without using JOIN. I have previously written article where I have compared INTERSECT with INNER JOIN I suggest that all user read that article for further clarity.


I would appreciate my readers input about this article and if you know any alternative method, it will be interesting to see them.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

75 thoughts on “SQL SERVER – Get Common Records From Two Tables Without Using Join

  1. Hello,

    I think this is much simpler,

    select A.column1, A.column2
    from table1 A, table2 B
    where A.column1 = B.column1

    The advantage of this is, you will get the complete result set, Intersect will not return duplicates.



    • I want to insert and update records from dummy table to main table with same attributes on to conditions are same.
      Example If Id and Date part are same in both table then records should be inserted
      If id is not same in dummy table then record should be inserted
      If id is same but date is not same in dummy then also records should be inserted


        • Table A Table B
          ————– —————-
          id id
          1 3
          2 4
          3 5


          id desc
          ———————- ————————
          1 1 Present in A
          2 2 present in B
          3 3 present in both
          4 4 prsent in B
          5 5 present in B

          please help me in this output.


          • Here is the script.

            create table #t1(id int)
            create table #t2(id int)

            insert into #t1 values(1),(2),(3)
            insert into #t2 values(3),(4),(5)

            select coalesce(t1.id,t2.id) as id,
            concat(coalesce(t1.id,t2.id),' present in ',case when t1.id is null then 'table 2' when t2.id is null then 'table 1' else 'both' end)
            from #t1 as t1 full join #t2 as t2 on t1.id=t2.id


  2. …but you still had to specify the column name in the intersect. So I’m not seeing the benefit or how this answers the challenge. Furthermore, I have to assume that this method is wildly inefficient compared to the inner join method because it probably can’t use the table indexes the same way due to the way the IN statement fetches and compares the data. I’d recommend you attach the estimated plans for the JOIN method versus the INTERSECT method.

    A “bad” answer to “how else can it be done?” would be to use SQL 87 syntax (which you also use the column name, but not the join syntax):
    SELECT tbl1.* FROM tbl1, tbl2 WHERE tbl1.Column1 = tbl2.Column1

    However, I believe the most natural way is, well, the natural join. This is as efficient as an inner join _and_ it satisfies the requirement of not specifying columns:
    SELECT tbl1.* FROM tbl1 NATURAL JOIN tbl2

    But before you go and try this join in MS SQL Server, MS SQL Server doesn’t support natural join syntax like that. Instead, it calls a natural join/equi-join a join where you use the equal operator on the ID columns within the INNER JOIN constraints.

    In closing, I think it’s important that you make an important distinction to your readers. While I understand showing that you _can_ do it, I think the important question to answer is _should_ you do it. In terms of efficiency and readability, I would still put my money on INNER JOIN. And I think you owe it to your readers to do some quick benchmarking to let them know if they should ever consider this approach over join syntax in real world situations.


  3. Hi,

    I have Emp_Id,EMP_Name in emp1 table and Emp_Id,Emp_Place in emp2 table, i want Emp_Name and Emp_Place from both the tables without using join.
    One more thing Emp_Id is a common column in both the tables.

    Can anybody help me?



  4. How can I join two table (with a query) that, one table has no data and the other has some data!!???

    this is because some times my data base may have a table with or with out data.


    I can’t use:

    SELECT comp.NAME, car.ID
    FROM comp, car

    it returns an empty table…


  5. hello…
    i got a problem..
    i want to join 2 table..
    1st table get data from source file and 2nd table get data
    from other source file…
    but many column from 2nd table must get data from 1st table..
    how can i do it?

    please help me



  6. Hi Farhan,
    Here is the query to get columnname and its count in all the tables

    SELECT name, COUNT(name) FROM sys.columns
    GROUP BY name

    This will give you the tablename and columnname

    SELECT t.name, c.name
    FROM sys.columns c INNER JOIN sys.tables t ON c.object_Id = t.object_id
    GROUP BY c.name, t.name



  7. Hello sir i m using sql 2000 in asp.net, i want to download a file from two tables indivuadually but i can do but i want from 2 tables Q1 and Q2, and download in csv format? wat can i do sir plz help me send me a mail regarding this thanks


  8. Hi ,

    I have 3 tables A,B and C where a , b and c have column name id , a .id is primary key , b.id and c.id is foreign key of a.id.

    For example
    A .Id has values 1,2,3,4
    B.id has values like 1,1,1,1,2,3,3,3,4
    C.id has values like 1,1,2,2,3,3,4

    I want result like
    a.id b.id c.id
    1 1 1
    1 1 1
    1 1 null
    1 1 null
    2 2 2
    2 null 2
    3 3 3
    3 3 3
    3 3 null
    4 4 4

    Please Help me…


  9. Hi,
    I have two tables in the same DB say A and B but there are no common column.
    but i suspect that some data are common.
    A.id = 1,2,3…..
    B.Value = 1,2,3

    Is there a way to find the common data in these tables??


  10. Hi,
    can any one help me…i want to eliminet the duplicate values while using INNER JOIN

    This is the code.

    ,H2.DESCA ‘JOB’
    ,H4.DESCA ‘JOB’


  11. hello ,
    I am new to your blog, but i found it really helping. I also have a query. i have two tables rib_entry(date,amtcash,amtcheque,total) and bank_entry(date,amtcredited,total) now i need to print a report where amtcredited=amtcash or amtcredited=amtcheque. the two table have many to many relationship. the question is how can i join them to show matching records only on their first occurence on date basis, and they should not be matched twice.
    i.e. if on 11/01/2010 amtcash=10000 and on 11/02/2010 amtcredited=10000 and on 11/02/2010 also amtcredited=10000 so the report should match 11/01/2010 amtcash to 11/02/2010 amtcredited only.


  12. I have Two tables in same database.Both table has a common column.Each columns has value like this (1,2,3).So How can i get value from one table matching both tables with common field.Any suggest me with query.


  13. Hi, I have an issue where i cannot joing two tables but i need to get the data from both of them. I can achieve this by using cross join or by inline query. But which eats the performance. So wanted to know is there any alternatives for the same.

    Table 1 contains required data say country names , region etc (no id’s) and the data is DB language specific. It may be in english in for one customer and German for another customer.

    I need to write a logic where if countryname=’India’ then 1 else 0 kind of thing.
    But as i said the values are in different lanaguages in differet DB i cannot compare this with harcoded text called ‘India’

    For that i have created another table Table 2 which contains data like country name in all the languages and language code.
    So i am writing query like below

    select case when test.countryname=test.b then 1 else 0 end
    select if Table1.countryname,(select table2.country name from table1 where language code=@parameter)b
    ) test

    This is giving the required results but i have 100 such columns where i need to do the comparison. Is there any better way to achieve this

    Thanks in advance


  14. Sir , This Is My Query

    With OrderedFoos As
    (Select row_number() over (order by Bill Asc) RowNum, Bill,BillDate,Partyname,Vat,Tax From Purchase Where BillDate Between ’09-01-2011′ And ’10-30-2011′ And Tax=’0′),
    OrderedBars as (Select row_number() over (order by Bill Asc) RowNum, Bill,BillDate,Partyname,Vat,Tax From Sales Where BillDate Between ’09-01-2011′ And ’10-30-2011′ And Tax=’0′)
    Select P.Bill PBILL,P.BillDate PBillDate,P.Partyname PParty,P.Vat PVat,S.Bill SBILL,S.BillDate SBillDate,S.Partyname SParty,S.Vat SVat From
    OrderedFoos P Inner join OrderedBars S on P.RowNum=S.RowNum

    .. Working But Purchase table 10 Rows ,Sales Table 5 Row .. this query retriving first 5 records only but i have purchase table 10 rows and sales table 5 rows without duplication … how do … Please Help Me…….


  15. Sir,, I Have two tables date, join the record two table record in date wise but

    first table record cout 10 and secord table record count 500 match two table date wise without duplication and null values

    How do Please Help me Sir…


  16. hi,

    I want to know how to find out a record from a database if we dont know the table name.

    eg. find out the record in which (field name / Attribute) city = ‘hyderabad’ in mydatabase.


  17. On the same note, I have three (3) tables A, B and C, each with same two columns Column1 and Column2, however I suspect that data inside the three tables may differ and I need to find the common data to the three tables, what would be the easier way to accomplish that?

    I was thinking: join A and B and put the result on a temp table X
    then join A and C and put the result on a table Z
    finally join X and Z and get my result.
    I believe there is a flow on that logic but I cannot point it out.
    Any response much appreciated.


  18. Quest:- I have four tables with a unique identity column and some more columns.i want to get all the columns into one row based on the id column.
    1) Data can be available in all the tables
    2)if not available in one of the tables the corresponding columns should show null or zero value.
    for ex:-tab1 , tab2 , tab3 , tab4 consists of three columns including unique id column..
    but data should come in one row…
    Result should be like this:-
    ID, tab1.col1,tab1.col2,tab2.col1,tab2.col2,tab3.col1,tab3.col2,tab4.col1,tab4.col2

    please help me this is a bit urgent.


  19. Hi sir i have a question related to sql query. i m developing a project on vb.net and in this i have to insert, update and delete and retrieve data in two tables in this both table have a equal column name that is File_Name. then how i perform this plsss. reply me.

    waiting for ur response



  20. Hi sir i have a question related to sql query. i m developing a project on vb.net and in this i have to insert, update and delete and retrieve data in two tables in this both table have a equal column name that is File_Name. then how i perform this plsss. reply me.

    waiting for ur response



  21. Hi Sir i am new to your blog and sql also.i want to merge three tables.is it possible to merge three tables?.
    id name
    1 xyz
    2 xyz



    i want to merge id=2 in id=1 and it update all child table.like,
    tb1 tb2 tb3
    id name id id
    2 xyz 2 2
    2 2


  22. hi all,

    i’m new to sql and i want to merge to tables:

    Table1: ddate1 Table2: ddate2

    table1 contains 2 data with 01/01/2012 while table 2 contains 4 data with 01/01/2012. Whenever i use left or right outer join the last data in table 1 repeats itself until it reaches 4 data. how can i prevent that? i use code: select ddate1, ddate2 from table1 left outer join table2 on ddate1 = ddate2.. Any idea on how to make my result like this:

    ddate1 ddate2
    01/01/2012 01/01/2012
    01/01/2012 01/01/2012
    null 01/01/2012
    null 01/01/2012


  23. hi all i have a doubt in sql server
    I have two tables like below

    table 1:
    name number
    a 2
    b 4
    c 2
    d 1

    table 2:

    name number
    a 4
    b 3
    f 5
    k 1

    result table1

    a 6
    b 7
    c 2
    d 1
    f 5
    k 1

    if column name is same for two tables that no should be add and store in first table table and the record with that name is not exist in first table that record should be add to first table.

    I am trying to got that but i didn’t got that .
    Plz send me that query if any body knows


  24. i have a table A

    id startno endno
    1 1 3
    2 3 5
    3 2 3

    and another table b


    want to have resultset :

    id No.
    1 1
    1 2
    1 3
    2 3
    2 4
    2 5
    3 2
    3 3

    how is it possible in sql? or we should use in sp with cursor??


    • declare @t table(id int, startno int, endno int)
      insert into @t
      select 1, 1, 3 union all
      select 2, 3, 5 union all
      select 3, 2, 3

      declare @number table(i int)
      insert into @number values (1),(2),(3),(4),(5)

      select id, i from @t as t1 cross join @number as t2 where i between startno and endno
      order by id,i


  25. Dear Dave & all

    There is two tables, 1st table contains one row for each id and 2nd table contains 20 rows for the respective id of 1st table, I need to show the 2nd table columns along with 1st table columns with repeat ids.

    Thanking you


  26. hi, i am new user to ur blog . my proble is that
    i have table thier is to column id and value like that
    id value
    1 a
    1 b
    1 c
    2 e
    2 f
    3 g
    i am want to out put like that
    1 a,b,c
    2 e,f
    3 g


  27. hello sir, i have created 2 forms one is to add the quantity of stock in my company and another is to dispatch the stock for particular users. when i dispatch the stock means that amount of stock should be automatically decreased in the stock view form. also if i add some other stocks means the form should be automatically updated. how can i do this in sql server 2005?


  28. How would if find records in table A (column name Proj_id) that are in another table B (column_name Proj_id) where B.Proj_id starts with A.Proj_ID

    The result would be all records in A with the matching records from B (but not exact), the results might look like the following (note A will have parent like values,while B will have an exact match to A, but also children that I want to include in the results)

    A.Proj_id B.Proj_id

    ABC ABC.000
    ABC ABC.001
    ABD ABD.000
    ABD ABD.001

    and so on


  29. Hello Please help,
    I have a database I am working on with 2 tables, Employees and Job_Title. I need to find the minimum and maximum salary of all employees that are exempt, and all the ones who are non-exempt. This requires me to pull from both tables, because the field Salary is in the Employee table, and the Field Status is in Job_Title. I have gotten the following query to return all data from both tables for the employees that are exempt, and I am assuming that it will work in reverse, if I change exempt to non-exempt, but I am not sure how to proceed in order to find the maximum salary based on this query’s results??

    Select * From Employee, Job_Title
    Where Status = ‘exempt’

    I was thinking like
    Create Table
    (Select * From Employee, Job_Title
    Where Status = ‘exempt’)

    and then take the Max (Salary) from that, but that give errors….
    Please give me any advice possible to complete this goal, and thanks in advance!!


  30. i have a problem i want to know query for this problem
    in one table field are e-id,e-name,e-salary and in second table have the same fields.
    now i have to find the e-id of these tables who has max salary.


  31. in one table have data(1,2,3,4,5,6,) and in second table have(4,5,6,7,8,9)
    using query i want this output (1,2,3,4,5,6,7,8,9)

    thank you.


  32. Hi,
    Suppose I have two tables with a few columns with a common name,now I need to Full Outer join ON Account_ID them such that I get everything in one table. But I dont want it to show the common columns twice ie Account_ID column should be one with rows of both tables (one after the other if the Account_ID r not matching)


  33. hii, suppose we have two tables A and B
    A has output like this

    B has output like this


    How can we get output like

    A_id B_name
    1 neha
    2 priya
    3 rashmi

    We can get the output using third table but how ? Please help..


  34. SELECT “invoice”.”ITEMNO”, “invoice”.”Outgoing”, “storeage”.”incoming” FROM “MVXJDTAP”.”MVXJDTA”.”invoice” INNER JOIN “MVXJDTAP”.”MVXJDTA”.”storeage ” ON “invoice”.”ITEMNO” = “storeage”.”itemno”
    ORDER BY”storeage”.”itemno”
    false RESULT :
    invoice storeage FALSE result
    itemno Outgoing itemno incoming itemno outgoing incoming
    ZOMV3-94 4 ZOMV3-94 3 ZOMV3-94 4 3
    ZOMV3-94 7 ZOMV3-94 1 ZOMV3-94 4 1
    ZOMV3-94 60 ZOMV3-94 4 60
    ZOMV3-94 5 ZOMV3-94 4 5
    ZOMV3-94 7 3
    ZOMV3-94 7 1
    ZOMV3-94 7 60
    ZOMV3-94 7 5
    ZOMV3-94 4 NULL OR 0
    ZOMV3-94 7 NULL OR 0
    ZOMV3-94 NULL OR 0 3
    ZOMV3-94 NULL OR 0 1
    ZOMV3-94 NULL OR 0 60
    ZOMV3-94 NULL OR 0 5


  35. Pingback: SQL SERVER – Weekly Series – Memory Lane – #051 | Journey to SQL Authority with Pinal Dave

  36. How to retrieve consecutive records from the table starting from particular location?
    e.g., There are 1000 records in the table. I want to retrieve records from location 750 to 775. Is there any way to do this?


  37. how i can retrive two different columns like(ename,deptname) from two different table like(emp,,dept) without using the join conditions
    ……………………help me


  38. Hello everyone. Need some help here. I have two tables with the same amount of columns and also the same column names. The first table has 29 rows and the second table has 31 rows. I want to filter the new rows (2) out based on a query. Intersect, NOT EXISTS do not work.
    My server version is 5.1.41 MySQL client version is 5.1.41 and my phpMyadmin version is 3.2.4. Please can anybody help me with a query?


  39. hello,

    i have created two tables named course name, student details. i have five records for each course. now i need out put as top three records of each course to be shown. send the query


  40. suppose i have three tables like tblStud,tblBatch,tblAttendance i have to show the name of student and status of the student. tblStud(studID,BatchID,StudName), tblBatch(batchID,batchName) tblAttedance(attID,studID,Status,Doa) when i select batch it will display Record here currentyly tblAttendane have no any data.


  41. I have two tables tbl1(cid,cname,caddress) and tbl2(vid,vname,vaddress).
    I need a query to display data from both tables as
    id,name,address which displays all data from tbl1 and tbl2.vid>10. can you help anybody?
    If question is not clear, plz mention.


  42. Hello,
    I need common records in 2 tables. There is no common identifier in those 2 tables. They have IDs present in both the tables BUT there relatioship is in the 3rd mapping table. What is the best way to get the output.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s