Common Mistakes
In this section, we have collected some common SQL mistakes and their resolutions to speed up your learning curve.
Example 1: Don’t narrow down the select scope via WHERE
As some tables are really big and include billions of rows, it’s better to narrow down the select scope with the WHERE clause. A common WHERE condition will be specifying the search dates.
Wrong SQL Example:
This SQL query without the data_creation_date
condition will take a much longer processing time than it supposes to.
SELECT
column_1,
column_2,
COUNT(1) AS COUNT
FROM
database.table
GROUP BY
1,
2
ORDER BY
1 DESC
LIMIT
100
Correct SQL Example:
SELECT
column_1,
column_2,
COUNT(1) AS COUNT
FROM
database.table
WHERE
data_creation_date = '2022-7-14'
GROUP BY
1,
2
ORDER BY
1 DESC
LIMIT
100
Example 2: Wrong date format
The correct date format should be ‘yyyy-mm-dd’ (instead of ‘yyyy-m-d’).
Cautions: Unlike other data, the Solana data is currently using the date format of ‘yyyy-m-d’, and it will be changed to ‘yyyy-mm-dd’ soon.
Wrong SQL Example:
This query will return 0 rows because data_creation_date
’s format is wrongly specified as ‘2022-07-04 instead of ‘2022-7-4’.
SELECT
column_1,
column_2
FROM
database.table
WHERE
data_creation_date = '2022-07-04'
LIMIT 100
Correct SQL Example:
SELECT
column_1,
column_2
FROM
database.table
WHERE
data_creation_date = '2022-7-4'
LIMIT 100
Example 3: Missing DATABASE name
Please always include the DATABASE name with the TABLE name. If you want to see a detailed explanation of the difference among DATABASE, and TABLE, please go to the Query Builder.
Wrong SQL Example:
SELECT
column_1,
column_2,
FROM
TABLE
Correct SQL Example:
SELECT
column_1,
column_2
FROM
database.table
Example 4: Incorrect wrapping of DATABASE and TABLE
You do not need to wrap the name of DATABASE and TABLE.
Wrong SQL Example:
SELECT
column_1,
column_2
FROM
`database`.`table`
Correct SQL Example:
SELECT
column_1,
column_2
FROM
database.table
Example 5: Select TABLE name as COLUMN name
Only column names are selectable.
Wrong SQL Example:
SELECT
table_name
FROM
database.table
If you want to see all the info from the COLUMNS, please use ‘*’ instead of the TABLE name.
Correct SQL Example:
SELECT
COLUMN
FROM
database.table
Correct SQL Example:
SELECT
*
FROM
database.table
Example 6: Incorrect Wrapping the Value
For a column value, you should wrap it with a single quote Don’t wrap it with a double quote or not wrap it at all.
Mistake: Use a double quote to wrap the value:
Wrong SQL Example:
SELECT
column
FROM
database.table
WHERE
column = "some_value"
Mistake: Not wrap the value at all:
Wrong SQL Example:
SELECT
column
FROM
database.table
WHERE
column = some_value
Correct SQL Example:
SELECT
COLUMN
FROM
database.table
WHERE
COLUMN = 'some_value'
Example 7: Missing GROUP BY
If you want to find the COUNT for each category, please add a GROUP BY at the end.
Wrong SQL Example:
SELECT
category,
COUNT(*) AS count_for_each_category
FROM
database.table
This SQL will bring an error because you cannot use aggregate functions in the WHERE clause.
What you should do is use the HAVING clause.
Wrong SQL Example:
SELECT
category,
COUNT(*) AS count_for_each_category
FROM
database.table
WHERE
COUNT(*) > 10
GROUP BY
category;
Correct SQL Example:
SELECT
category,
COUNT(*) AS count_for_each_category
FROM
database.table
GROUP BY
category
HAVING COUNT (*) > 10
These examples showcase the most common mistakes that people often make. By practicing writing SQL, you will quickly learn from your own mistakes!
Updated 9 months ago