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
INTERSECT
SELECT
column1
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
INTERSECT
SELECT
column1
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.

SQL SERVER – 2005 – Difference Between INTERSECT and INNER JOIN – INTERSECT vs. INNER JOIN

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)

About these ads

66 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.

    Thanks,
    Imran.

    Like

  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.

    Like

  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?

    Regards,
    Sandeep

    Like

  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.

    thanks

    I can’t use:

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

    it returns an empty table…

    Like

  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?
    huhuhuh

    please help me

    tq

    Like

  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

    Thanks
    Nizam

    Like

  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

    Like

  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…

    Like

  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??

    Like

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

    This is the code.

    SELECT DISTINCT H1.EMP_CODE,H1.NAME1A+’ ‘+H1.NAME2A+’ ‘+H1.NAME3A ‘NAME’
    ,H2.DESCA ‘JOB’
    ,H3.DESCA ‘COUNTRY’
    ,H4.DESCA ‘JOB’
    FROM H_EMP H1 INNER JOIN H_D_JOB H2
    ON H1.JOB_CODE=H2.CODE
    INNER JOIN H_D_NATIONALITY H3
    ON H1.NAT_CODE=H3.CODE
    INNER JOIN H_D_DEPARTMENT H4
    ON H1.DEP_CODE=H4.CODE ORDER BY EMP_CODE

    Like

  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.

    Like

  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.

    Like

  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
    from
    (
    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

    Like

  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…….

    Like

  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…

    Like

  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.

    Like

  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.

    Like

  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.

    Like

  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

    Plzz.

    Like

  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

    Plzz.

    Like

  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?.
    table1
    ———–
    id name
    1 xyz
    2 xyz

    tale2
    ——–
    id
    1
    2

    table3
    ——–
    id
    1
    2

    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

    Like

  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

    Like

  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

    Like

  24. i have a table A

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

    and another table b

    No
    1
    2
    3
    4
    5

    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??

    Like

    • 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

      Like

  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
    Kishor

    Like

  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

    Like

  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?

    Like

  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
    ABC ABC.000
    ABC ABC.001
    ABD ABD
    ABD ABD.000
    ABD ABD.001

    and so on

    Like

  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!!

    Like

  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.

    Like

  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.

    Like

  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)

    Like

  33. hii, suppose we have two tables A and B
    A has output like this
    A_id
    1
    2
    3
    4
    5

    B has output like this
    B_name

    neha
    priya
    rashmi

    How can we get output like

    A_id B_name
    1 neha
    2 priya
    3 rashmi
    4
    5

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

    Like

  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
    HOW CAN GET THIS RESULT
    TRUE RESULT :
    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
    I WANT CODE TO RUN TRUE RESULT

    Like

  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?

    Like

  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

    Like

  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?

    Like

  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

    Like

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