SQL:Select

lastmod 2021-11-16created 2021-10-248 edits

IN operator syntax

• PostgreSQL filtered queries (IN operator)

An operator that determines whether a given set or list exists in a given set (column or list).

Check whether values such as value1, value2 exist in the set held by column_name:

select * from table_name where column_name in(value1,value2,...)

Check whether the set of column_name2 from table table_name2 exists in the set held by column_name:

select * from table_name where column_name in
(select column_name2 from table_name2);

Removing duplicates

• (SQL) Removing duplicates (DISTINCT, GROUP BY)

To write a query that removes duplicates, use either #DISTINCT or #GROUP BY.

SELECT DISTINCT CITY FROM TABLE WHERE CITY_CODE = 02;

SELECT CITY FROM TABLE WHERE CITY_CODE = 02 GROUP BY CITY;

• DISTINCT : Removes duplicates but does not sort.
• GROUP BY : Removes duplicates and also sorts.

Because GROUP BY performs not only grouping but also sorting, DISTINCT can be faster. Use whichever fits the situation.

DISTINCT

• [Recommended] How we made DISTINCT queries up to 8000x faster on PostgreSQL - Why DISTINCT queries are slow and how to search fast in TimescaleDB

RECURSIVE CTEs

• Loose indexscan - PostgreSQL wiki

WITH RECURSIVE cte AS (
   (SELECT tags_id FROM cpu ORDER BY tags_id, time DESC LIMIT 1)
   UNION ALL
   SELECT (
      SELECT tags_id FROM cpu
      WHERE tags_id > t.tags_id 
      ORDER BY tags_id, time DESC LIMIT 1
   )
   FROM cte t
   WHERE t.tags_id IS NOT NULL
)
SELECT * FROM cte LIMIT 50;

GROUP BY

See also

• SQL