SQL Tips & Tricks

SQL Tips & Tricks

SQL Tips & Tricks

Below are few details which will add value to your SQL knowledge.

• Count(*) vs Count(1)
  • COUNT(SomeColumn) will only return the count of rows that contain non-null values for SomeColumn.
  • COUNT(*) returns the count of all records in the table, doesn't matter if the portions of records are null or not.
  • COUNT(1) populates 1 in all records (null as well as not null) for the sake of result set, and hence returns the total record count.
  • COUNT(*) and COUNT('Foo') will return the total number of rows in the table.
• Checking for duplicates

  select * from table where rowid not in (select max(rowid) from table group by )
  

• Exists/Not Exists are expensive operations and can be replaced by joins. Example:

  --old: 4 h 45 m
  SELECT /*+ parallel (a,4) */
  a.colOne, a.colTwo, a.colThree,
  a.colFour
  FROM tableOne a
  WHERE NOT EXISTS(
  SELECT 1
  FROM tableTwo c
  WHERE a.colOne = c.colOne
  AND a.colTwo = c.colTwo
  AND a.colFour = c.colFour)
  
  --new: 2 m 21 s
  SELECT /*+ parallel (a,8) parallel(c, 8) use_hash(c, a) */
  a.colOne, a.colTwo, a.colThree,
  a.colFour
  FROM tableOne a, tableTwo c
  WHERE a.colOne = c.colOne(+)
  AND a.colTwo = c.colTwo(+)
  AND a.colFour = c.colFour(+)
  AND c.rowid is null;
  

• CSV Record from SQL SELECT using concatenation

  SELECT table_name || ',' ||column_name || ',' || data_length || ',' || data_type || ',' || 'Added in New Swap' as message from cols;