(My)Sql statements

Find records in table Orphan that don’t exist in table OrphanRef:

SELECT * FROM Orphan LEFT JOIN OrphanRef ON OrphanRef.Id=Orphan.Id WHERE ISNULL(OrphanRef.Id)

Delete records in table¬†Orphan that don’t exist in table OrphanRef:

DELETE Orphan FROM Orphan LEFT JOIN OrphanRef ON OrphanRef.Id=Orphan.Id WHERE ISNULL(OrphanRef.Id)

Check on a NULL value:

  • SELECT * FROM tbl_name WHERE col_name IS NULL
  • SELECT * FROM tbl_name WHERE ISNULL(col_name)

Subquery with a LEFT JOIN:

SELECT * FROM table1 WHERE table1.id NOT IN (
SELECT table3.id FROM table2 LEFT JOIN table3 ON table3.id = table2.id)

Find duplicate records in a table using a single field:

SELECT t1.* FROM ‘tablename’ t1 INNER JOIN ‘tablename’ t2 ON t1.field1=t2.field1 AND t1.id <> t2.id

Find duplicate records in a table using two fields:

SELECT t1.* FROM table t1 INNER JOIN table t2 ON t1.field1=t2.field1 AND t1.field2=t2.field2 AND t1.id <> t2.id


Leave a Reply