SQL Best Practices

In a query , If there is more then one condition then Write AND / OR in between them.

Instead of double == , use single = .

Example:

 SELECT product_id from Products where low_fats = 'Y' and recyclable = 'Y'

Link question

We can use != or <> for negation.

Keep in mind that null values cant filter out using <>

For example..

Customer =

| id | name | referee_id | | --- | --- | --- | | 1 | Will | null | | 2 | Jane | null | | 3 | Alex | 2 | | 4 | Bill | null | | 5 | Zack | 1 | | 6 | Mark | 2 |

 SELECT name from Customer where  referee_id <> 2 OR referee_id is NULL

So Here If I want only referee_id <> 2 will not give me below result.

| name | | --- | | Will | | Jane | | Bill | | Zack |

Here we need to use specifically referee_id is NULL

ORDER BY (author_id) , to sort the result on basis of author_id

Note: Here author_id should be used after ORDER BY

We can use LENGTH('string') to calculate length of a String if characters are of English, If they are of latin or other countries then it will return more then 1

Example LENGTH('€') # is equal to 3

The more accurate way to calculate the charters in string is CHAR_LENGTH(), which will count the number of characters.

Example: CHAR_LENGTH('€') is equal to 1

While performing joins we can use USING(column_name) , if the column name is same in both of the table