SQL Server : two columns with the same row values in the same table

Question

I have a huge dataset with about 400,000 rows. I want to select only the rows where the value of the second column (val) exists in the third column (ecr) in the same table.

For example, in the sample screenshot shown below, the value of the column val on the second row (4294939057) is equal to third row value of column ecr.

I tried with the following query but it doesn't seem to give the correct rows.

Any tips would be very much appreciated.

use dbTest

select val, ecr 
from tableTest 
group by val 
having COUNT (val) > 1

I am using SQL Server 2008.

do you have a primary key column on this table?

M.Ali
– M.Ali

2017-02-20 17:54:12 +00:00
Commented Feb 20, 2017 at 17:54 — M.Ali
– M.Ali, Commented Feb 20, 2017 at 17:54

SqlZim · Accepted Answer · 2017-02-20 18:53:30Z

3

You should use a self join (could be you need proper separated index on val and ecr due the dimension of your table )

select a.*, b.* 
from tableTest as a 
  inner join tableTest as b  
    on a.val  = b.ecr

edited Feb 20, 2017 at 18:53

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

answered Feb 20, 2017 at 17:54

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SqlZim Over a year ago

@DestaHaileselassieHagos change where to on

SqlZim · Accepted Answer · 2017-02-20 18:56:51Z

2

If you do not want the full output from an inner join, you could use something like this:

select *
from tableTest as t
where exists (
  select 1 
  from tableTest as i  
  where t.val  = i.ecr
  )

answered Feb 20, 2017 at 18:56

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

4 Comments

Gordon Linoff Over a year ago

This is the better solution, because it does not multiply the number of rows.

Desta Haileselassie Hagos Over a year ago

This is actually better where you don't have to show all the output of the inner join.

Desta Haileselassie Hagos Over a year ago

@sqlzim, But why select 1?

SqlZim Over a year ago

@DestaHaileselassieHagos There is no runtime performance difference between select 1 and select * when using exists(). Although using select 1 will avoid having to examine any unneeded metadata for that table during query compilation. EXISTS Subqueries: SELECT 1 vs. SELECT * - Conor Cunningham

asemprini87 · Accepted Answer · 2017-02-20 18:04:23Z

1

The other option besides the join is a subquery:

SELECT *
FROM tableTest
WHERE val IN (SELECT ecr FROM tableTest)

answered Feb 20, 2017 at 18:04

asemprini87

3472 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Server : two columns with the same row values in the same table

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related