Check if composite primary key doesn't exist in a different table-CodePudding

I'm using sql server. Currently I have 2 tables. I want to check if the composite PK from table 1 is not contained in table 2.

I tried using this but unfortunately it does not work in sql server. In this case the PK is composed of id1 and id2.

SELECT * FROM TABLE_1 WHERE (id1, id2) NOT IN (SELECT id1, id2 FROM TABLE_2)

I have seen more complicated solutions to this as well but, as I am trying to generate these queries as part of a python program I was hoping for an easy solution like the 1-line above.

CodePudding user response：

I'd use an EXISTS() check...

SELECT
  table_1.*
FROM
  table_1
WHERE
  NOT EXISTS (
    SELECT *
      FROM table_2
     WHERE table_2.id1 = table_1.id1
       AND table_2.id2 = table_1.id2
  )

In terms of a query being a single line, I don't recommend single line queries. As a principle code should be long and narrow, not short and wide, and column references should always be qualified (such as with a table name or alias)...

For example, your non-working query could be laid out as follows, and suddenly doesn't appear meaningfully shorter than a NOT EXISTS() based query (while also being significantly more maintainable, and more friendly to diff tools, such as used by git, etc).

SELECT
  table_1.*
FROM
  table_1
WHERE
  (
    table_1.id1,
    table_1.id2
  )
  NOT IN
  (
    SELECT
      table_2.id1,
      table_2.id2
    FROM
      table_2
  )

I too generate SQL from python (be it through an ORM or my own abstraction layer), and neat queries are no harder to generate than "short single line queries".