1

Let's say you have table A

CREATE TABLE A (
  id int,
  attribute varchar(10)
)

CREATE TABLE B (
   id int,
   parent_id int,
   attribute varchar(10)
)

parent_id is a link to the id column in A. I would like all rows from A (all columns), if B.attribute = "Test". Note that there may be multiple B records for each A, but I only want the DISTICT A. I know I could do this, but it seems like it would not be performant.

SELECT DISTINCT a.id, a.attribute
FROM A a JOIN B b ON a.id = b.parent_id
WHERE b.attribute = "Test
bpeikes
  • 3,495
  • 9
  • 42
  • 80

2 Answers2

3

generally EXISTS operates faster than IN() so I usually prefer this query over others. try it out!

SELECT a.id, a.attribute
FROM A a
WHERE EXISTS
(   SELECT 1
    FROM B b
    WHERE a.id = b.parent_id
      AND b.attribute = 'Test'
);

DEMO

John Ruddell
  • 25,283
  • 6
  • 57
  • 86
1

Using IN will perform better than your query. However,The best alternative is EXISTS as @John Ruddell said in his answer

    SELECT a.id,
       a.attribute
FROM   A AS a
WHERE  a.id IN (SELECT parent_id
                FROM   B AS b
                WHERE  b.attribute = "Test");

EXISTS is more faster because once the engine has found a hit, it will quit looking as the condition has proved true. With IN it will collect all the results from the subquery before further processing.(SO)

SELECT a.id, a.attribute
FROM A a
WHERE EXISTS
(   SELECT 1
    FROM B b
    WHERE a.id = b.parent_id
      AND b.attribute = 'Test'
);
Community
  • 1
  • 1
Subin Jacob
  • 4,692
  • 10
  • 37
  • 69