Are this good MySQL practices

Question

When I make a SQL query, for example, in database where there's a table named "employees", which is the best practice of writing?

SELECT 'name', 'surname', 'phone' WHERE 'city'='ny' FROM 'employees' ORDER BY 'name'

SELECT name, surname, phone, WHERE city=ny FROM employees ORDER BY name

or

SELECT employees.name, employees.surname WHERE employees.city=ny ORDER BY employee.name

And why? Is there a standard for this?

At first, you should add `FROM employee` to your statements ;) — dertkw, Jul 27 '11 at 06:46

score 2 · Answer 1 · edited May 23 '17 at 12:26

2

SELECT `name`, `surname`, `phone` WHERE `city`='ny' ORDER BY `name`.

Note there's a difference between ` and ' (the first is used for the name of fields, and the other one is for strings). Although the ` symbol is only strictly necessary e.g. when name has special characters or if name is an SQL keyword.

edited May 23 '17 at 12:26

Community

1
1

answered Jul 27 '11 at 06:49

Nooneshall

21
1

The symbol used for column escaping depends on ANSI_QUOTES switch. – Karolis Jul 27 '11 at 07:14

score 1 · Answer 2 · answered Jul 27 '11 at 06:52

The aim is to make your SQL as readable as possible, both for yourself and for others who may encounter it at a later date.

Below its clear

That my value for city is a string
What I'm selecting, filtering on, what tables I'm using and what I'm sorting by

Only use quotes around your field names if they contain spaces (or other special characters). Otherwise, don't use them as it makes your code messy.

SELECT 
   name, 
   surname, 
   phone
FROM 
   employees 
WHERE
   city='ny'
ORDER BY 
   name

Konerak · Answer 3 · 2011-07-27T07:01:33.120

1

Though it's mostly a matter of personal style, some forms have their advantages. My preference:

SELECT e.`name`, e.`surname`, e.`phone` 
FROM `employees` e
WHERE e.`city`= 'ny' 
 OR e.`city` = 'wa'
ORDER BY e.`name`

Keywords in uppercase, tablenames in lowercase (if you create your tables lowercase or have set them to be case-insensitive)
Each keyword on a different line
Each table gets an alias (but without the explicit AS: employees AS e)
Always specify the table name before a column name. This way, you safely can add other tables that possibly have columns with the same name without worries.

Another example:

SELECT e.`name`, e.`surname`, e.`phone`, u.rank
FROM `employees` e
[INNER] JOIN `unionreps` u
ON e.ID = u.ID

JOINs are written in ANSI-92 style, not ANSI-89 (from e,u where e.id=u.id)

edited Jul 27 '11 at 07:01

answered Jul 27 '11 at 06:56

Konerak

39,272
12
98
118

2

+1 These are generally my preferences too. Although I don't use backticks if I can help it. If designing the DB myself, I make sure that table and column names are not reserved words, and don't contain any characters that require the use of backticks. – Mike Jul 27 '11 at 07:05
I'm not typing them when I do a quick ad-hoc query to check something, but my application code usually does :) – Konerak Jul 27 '11 at 12:08

Devart · Answer 4 · 2011-07-27T07:10:00.240

0

For me:

when one table is involved - I do not use table aliases.
when some tables are involved into query - I use table aliases.

For example:

SELECT deptno, dname FROM dept WHERE loc = 'NEW YORK'

or

SELECT d.deptno, d.dname, e.ename, e.job FROM dept d
JOIN emp e ON d.deptno = e.deptno
WHERE d.loc = 'NEW YORK'

Big query will look clearer and shorter with table aliases.

edited Jul 27 '11 at 07:10

answered Jul 27 '11 at 06:54

Devart

119,203
23
166
186

score 0 · Accepted Answer · answered Jul 27 '11 at 08:04

IMHO, the best standard is this:

select
    o.name as office_name,
    e.name as employee_name,
    count(*) as count
from employee e
left join office o on o.id = e.office_id
where e.name like 'a%'
group by 1
order by 1

Your goal is improved clarity and maintenance. The features/benefits demonstrated here are:

Nothing in uppercase (ie select not SELECT)
Only use backtick escapes for reserved words
Table names in singular (ie employee not employees)
View names in the plural (eg current_employees)
Underscores separating name parts (ie no camelCase or flatcase etc)
Primary keys always named id
Foreign keys always named table_id
Where column names collide in a query, aliased them as <table>_<column>
Queries are formatted as above
- Left justified, except columns, which are eash on their own line
- Line breaks on major keywords
Table aliases generally using all first letters of name parts, eg my_table_name mtn
Group by and order by preferring numbers over expressions (if you change the expression, you don't need to the group by or order by clauses, otherwise you're violating the DRY principle)

This comes from many years of coding SQL and I've found this makes things easiest.

If you always have a consistent format, you'll find syntax errors easier to find too.

Very useful answer, too bad I have to change A LOT of things already done. Thanks! — Mariano Avopa, Jul 29 '11 at 07:57

score 0 · Answer 6 · answered Jul 27 '11 at 11:12

As others have said, its a matter of coding style. Personally I prefer not to use reserved words for entity names - it makes the code more portable and prevents confusion like:

SELECT 'name', 'surname', 'phone' WHERE 'city'='ny' FROM 'employees' ORDER BY 'name'

Which is just gobbledgook; mysql uses backticks for encapsulating entity references, not single quotes. OTOH Oracle uses double quotes. When you use delimiters the referencing becomes case sensitive.

The programming standard I use is to always prefix column names with the table name or alias (more often the latter to reduce the amount of typing) to make it clearer what the code is intended to do. I also prefix my table names with the database name - switching between databases using the state mechanism (USE some_db;) is just messy:

SELECT e.name
, e.surname
, e.phone
FROM avopa.employees
WHERE e.city='ny'
ORDER BY e.name;

Note that SQL keywords are in UPPER case, entity references in LOWER case, one expression per line

Are this good MySQL practices

6 Answers6