What are your top SQL tips?

Comments

[Aaron Reese]

1) code layout: SELECT, FROM, WHERE un-tabbed. JOIN indent 1 tab. Fields and tables indent 5 or 6 tabs. AND/OR in WHERE clause indent one less than conditions. If you have complex nested conditions use brackets and indentation to make it clear.
2) SARGable conditions (Google them)
3) no nested selects, lift them out to be CTEs. It makes them easier to test and keeps your main code cleaner because you can give the CTE a decent name when you include it in the main query.
4) don't use CURSOR unless you absolutely have to. Most instances could be replaced by PIVOT/UNPIVOT or STUFF...FOR XML

[Ricardo Giro]

Hey Aaron, would you happen to have a visual example of your first point?

[Aaron Reese]

SELECT
                COUNT(EP.ID) AS [Sellers]
                ,EP.EventID,
                ,T.TrailName,
                ,TE.EventDate
FROM
                EventParticipant AS EP
    JOIN
                TrailEvent AS TE ON TE.ID = EP.EventID
    JOIN
                Trail AS T ON T.ID = TE.TrailID
WHERE
                EP.ParticipantType = 'Seller'
            AND
                TE.EventDate >GETDATE()
GROUP BY
                EP.EventID
                ,TE.EventDate
                ,T.TrailName
ORDER BY
                COUNT(EP.ID) DESC

`

[Ricardo Giro]

Cool, that's very easy to read! Thanks

[Leo Antony]

Don't use SELECT * from TABLE;

[Aaron Reese]

because....?

[Leo Antony]

Because * will return every single column , when you only need 1-2 column

[Aaron Reese]

you are right, but that is not why you shouldn't use * in a production query. Not specifying the columns requires a round trip to the sys schema to get a list of columns so it is fractionally slower but more importantly if you are using it in a view and the structure of the underlying table changes the new columns will NOT be reflected in the view until you recompile it using an ALTER VIEW command and this can be very confusing. When you do recomile it then the ordinal position of subsequent columns in the view will be shifted, or you may end up with naming conflicts which are hidden in the original query by not listing the field names explicitly.

In a similar vein make sure you use column names

SELECT  firstCol,thirdCol FROM Table

rather than ordinal positions

SELECT 1,3 FROM Table

as the column order COULD change, especially if you are referencing a VIEW rather than a TABLE not to mention that it is significantly easier to read with column names

[Leo Antony]

wow , You explained everything😲

[Tomas@PawSQL]

Accuracy Rules

• Using "= null" or "case when null" cannot determine if an expression is empty. Use "is null" to determine if an expression is empty(PawSQL provides a rewrite optimization).
• If a subquery with "NOT IN" has a nullable selected column, it will not return any records(PawSQL provides a rewrite optimization).
• If a subquery with "ALL" has a selected column that is empty, it will not return any records(PawSQL provides a rewrite optimization).
• Using "LIMIT" in an "UPDATE" statement can lead to unexpected results. (PawSQL provides a warning alert.)
• Using "LIMIT" in a "SELECT" statement without "ORDER BY" can lead to inconsistent results when executed multiple times. (PawSQL provides a warning alert.)
• Using "UPDATE", "MERGE", or "DELETE" statements without a "WHERE" clause or with a "WHERE" clause such as "WHERE 1=1" (which effectively has no conditions) should be avoided. (PawSQL provides a warning alert.)
• When using an "INSERT" statement, column names should be specified to reduce the possibility of mismatching columns and result tables. (PawSQL provides a warning alert.)

Performance Rules

• Avoid using full-text search or left-matching search（'%abc%' or 'abc%'）, which cannot quickly locate records using indexes. (PawSQL provides a warning alert.)
• Avoid using functions on conditional fields, which prevent the use of indexes and result in full table scans(PawSQL provides a rewrite optimization).
• Implicit conversion of numeric and character types in conditional fields prevents the use of indexes and results in full table scans(PawSQL provides a rewrite optimization).
• Avoid using "SELECT *" syntax, which affects subsequent optimization logic and is difficult to maintain. (PawSQL provides a warning alert.)
• Avoid using scalar subqueries with "MAX" or "MIN" and rewrite them to take advantage of the ordered nature of B+ trees to avoid sorting and accessing unnecessary data(PawSQL provides a rewrite optimization).
• Scalar subqueries with "COUNT(*)>0" or "COUNT(col)>0" should be rewritten as "EXISTS" to avoid accessing unnecessary data(PawSQL provides a rewrite optimization).
• Use "UNION ALL" instead of "UNION", as "UNION" requires grouping and de-duplication, which affects performance. (PawSQL provides a warning alert.)
• "DELETE" or "UPDATE" statements with "OR" conditions should be split into multiple independent "UPDATE" or "DELETE" statements, as conditions with "OR" may not be able to use indexes(PawSQL provides a rewrite optimization).
• Use "UNION" or "UNION ALL" instead of "OR", as conditions with "OR" may not be able to use indexes(PawSQL provides a rewrite optimization).
• Push conditions down by filtering first and grouping later (moving the "HAVING" clause to the "WHERE" clause) to improve performance(PawSQL provides a rewrite optimization).
• "DELETE" or "UPDATE" statements should not have "ORDER BY" without "LIMIT", as sorting without a limit is meaningless and can decrease performance(PawSQL provides a rewrite optimization).
• Avoid using queries without a "WHERE" clause or with a "WHERE" clause such as "WHERE 1=1" (which effectively has no conditions), without grouping or aggregating, and without "LIMIT"(PawSQL provides a rewrite optimization).

Create Proper Indexes

• Quick positioning of data records (using equality or range conditions)
• Avoiding sorting operations (such as order by, group by, distinct, union, and sort merge join)
• Avoiding table lookups, which means completing the query operation by only accessing index files without accessing data tables.

[Andrew Baisden]

Use a good CLI tool like Pgcli and a GUI like Valentina Db.

[KarmaBlackshaw]

Bulk update/insert 👌

[🦉 Leila Borges]

Tentei aprender SQL, consegui travar o note mas não consegui instalar a senha mestra, agora vou tentar aprender de novo.

[Parker Waiters]

SQL Tips & Tricks: Counting Rows

Jimmy Guerrero for YugabyteDB ・ Aug 27 '20

#postgres #yugabyte #sql #distributedsystems