Where Col1,Col2 in (...) [SQL Subquery Using Composite Primary Key]

WHERE col1,col2 IN (...) [SQL subquery using composite primary key]

sqlite> create table foo (a,b,c);
sqlite> create table bar (x,y);
sqlite> select * from foo where exists (select 1 from bar where foo.a = bar.x and foo.b = bar.y);

Replace the select 1 from bar with your select ... many tuples of a/b values ....

Or create a temporary table of your select ... many tuples of a/b values ... and use it in place of bar..

Performance on SELECT with composite primary key's element

According to the PostgreSQL documentation:

A multicolumn B-tree index can be used with query conditions that
involve any subset of the index's columns, but the index is most
efficient when there are constraints on the leading (leftmost)
columns. The exact rule is that equality constraints on leading
columns, plus any inequality constraints on the first column that does
not have an equality constraint, will be used to limit the portion of
the index that is scanned.

You can also try running an explain plan on your query to determine this behavior.

Remove duplicate rows where col1 and col2 are equal but reversed

As you need to treat some rows in your table as duplicates, GROUP BY is the way to go: the trick is to build up a GROUP BY expression so that { X, Y } and { Y, X } tuples are treated as the same value. Here's one possible approach:

GROUP BY IF(
  ANTECEDENT > CONSEQUENT, 
  CONCAT(ANTECEDENT, "+", CONSEQUENT), 
  CONCAT(CONSEQUENT, "+", ANTECEDENT) 
)

After that you can use this GROUP BY to fetch data the way you need (and it's not clear what exactly you need here, which of two rows should be excluded). Here's one way to show only the "first" rows of the duplicate sets:

SELECT *
  FROM your_table
 WHERE rule_id IN (
    SELECT MIN(rule_id)
      FROM your_table
  GROUP BY IF(
             ANTECEDENT > CONSEQUENT, 
             CONCAT(ANTECEDENT, "+", CONSEQUENT), 
             CONCAT(CONSEQUENT, "+", ANTECEDENT) 
           )
       )

Composite key in SQLite

Put a PRIMARY KEY (col1, col2, col3) constraint in the CREATE TABLE statement. SQLite doesn't support changing a table's primary key after it's created (which is a bit of a silly idea anyway).

Delete based on composite key from another table

This can be cleanly performed using JOIN with your DELETE:

DELETE a
FROM 
    Table1 a 
    JOIN Table2 b
        ON a.Col1 = b.Col1
        AND a.Col2 = b.Col2

---

Related Topics