SQL Tutorial and Tricks

COUNT Function ​

COUNT(*), COUNT(1), COUNT(column_name) ​

• COUNT(*): 统计所有行，不管是否为 NULL
• COUNT(1): 统计所有代码行，不管是否为 NULL
• COUNT(column_name): 统计指定列的行数，判断是否为 NULL

Speed ​

• COUNT(*) 和 COUNT(1) 的速度是一样的，因为 COUNT(*) 会统计所有列，而 COUNT(1) 会统计所有代码行
• COUNT(*) 是SQL92定义的标准统计数的语法，所以速度会快一些
• COUNT(column_name) 会判断是否为 NULL，所以速度会慢一些

USING Function ​

The USING clause is used to specify some condition to join tables.

SELECT * FROM table1 JOIN table2 USING (column_name);
-- equal to
SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

ORDER BY Clause ​

ORDER BY 1 ​

order by the first column in the select list.

SELECT * FROM table_name ORDER BY 1;
SELECT first_column, second_column FROM table_name ORDER BY 1;

JOIN Clause ​

JOIN Cheat Sheet ​

Reference ​

• MySQL：SELECT COUNT 小结
• Learn SQL
• MySQL Documentation
• SQL Tutorial