\nSELECT<\/strong>\r\n R.rental_date, C.customer_id, C.first_name, C.last_name\r\nFROM<\/strong>\r\n rental R\r\n JOIN<\/strong> customer C USING<\/strong> (customer_id)\r\nWHERE<\/strong>\r\n R.rental_date >= DATE('2005-10-01')\r\n AND<\/strong> C.store_id=1;\r\n<\/pre>\n<\/blockquote>\nThe above looks for film rentals done in a specific store (store #1<\/strong>), as of Oct. 1st, 2005.<\/p>\nQuery #2<\/h4>\n\nSELECT<\/strong>\r\n F.title, C.name\r\nFROM<\/strong>\r\n film AS<\/strong> F\r\n JOIN<\/strong> film_category AS<\/strong> S ON<\/strong> (F.film_id = S.film_id)\r\n JOIN<\/strong> category AS<\/strong> C ON<\/strong> (S.category_id = C.category_id)\r\nWHERE<\/strong> F.length > 180;<\/pre>\n<\/blockquote>\nThe above lists the title and category for all films longer than three hours.<\/p>\n
Query #3<\/h4>\n\nSELECT<\/strong> c.customer_id, c.last_name\r\nFROM<\/strong>\r\n customer c\r\n INNER JOIN<\/strong> address a ON (c.address_id = a.address_id)\r\n INNER JOIN<\/strong> (\r\n SELECT<\/strong>\r\n c.city_id\r\n FROM<\/strong>\r\n city AS c\r\n JOIN<\/strong> country s ON<\/strong> (c.country_id = s.country_id)\r\n WHERE<\/strong>\r\n s.country LIKE<\/strong> 'F%'\r\n ) s1 USING<\/strong> (city_id)\r\nWHERE<\/strong>\r\n create_date >= DATE('2005-10-01');\r\n<\/pre>\n<\/blockquote>\nThe above lists customers created as of Oct. 1st, 2005, and who live in countries starting with an ‘F’. The query could be solved without a subquery, but there’s a good reason why I made it so.<\/p>\n
The problems<\/h4>\n
I used very different conventions on any one of the queries, and sometimes within each query. And it’s common that I see the same on a customer’s site, what with having many programmers do the SQL coding. Again, I will only discuss the table aliases conventions. I’ll leaver the rest to the reader.<\/p>\n
Here’s where I see problems:<\/p>\n
\n- Query #1<\/strong>: In itself, it looks fine. Rental<\/strong> turns to R<\/strong>, Customer<\/strong> turns to C<\/strong>. I will comment on this slightly later on when I provide my full opinion.<\/li>\n
- Query #2<\/strong>: So film<\/strong> turns to F<\/strong>, category<\/strong> turns to C<\/strong>. What should film_category<\/strong> turn into? Out of letters?<\/em> Let’s just go for S<\/strong>, shall we? But S<\/strong> has nothing do with film_category<\/strong>. Yet it’s so commonly seen.<\/li>\n
- Query #2<\/strong>: We’re using the AS<\/strong> keyword now. We didn’t use it before.<\/li>\n
- Queries #1<\/strong>, #2<\/strong>: Hold on. Wasn’t C<\/strong> taken for customer<\/strong> in Query #1<\/strong>? Now, in Query #2<\/strong> it stands for category<\/strong>? I’m beginning to get confused.<\/li>\n
- Query #3<\/strong>: Now aliases are lower case; I was just getting used to them being upper case.<\/li>\n
- Query #3<\/strong>: But, hey, c<\/strong> is back to customer<\/strong>!<\/li>\n
- Query #3<\/strong>: Or, is it? Take a look at the subquery. Theres another c<\/strong> in there! This time it’s city<\/strong>! And it’s perfectly valid syntax. We actually have two identical aliases in the same query.<\/li>\n
- Query #3<\/strong>: If I could, I would name country with c<\/strong> as well. But I can’t. So why not throw in s<\/strong> again?<\/li>\n
- Query #3<\/strong>: and now I don’t even bother using the alias when accessing the create_date<\/strong>. Well, there’s no such column in any of the other tables!<\/li>\n<\/ul>\n
Proper conventions<\/h4>\n
What I find so disturbing is that whenever I read a complex query, I need to go back and forth, back and forth between table aliases (found everywhere in the query) and their declaration point. Such irregularities make the queries difficult to read.<\/p>\n
Any of the above issues could be justified. But I wish to make some suggestions:<\/p>\n
\n- Decide whether you’re going for upper or lower case.<\/li>\n
- Do not use the same alias twice in your query, even if it’s valid.<\/li>\n
- Aliases do not have to be single character. film_category<\/strong> may just as well be FC<\/strong>.<\/li>\n
- Do not alias something that is hard to interpret. s<\/strong> does not stand for country<\/strong>.<\/li>\n
- Think ahead: use same aliases throughout all your queries, as far as you can. If uniqueness is a problem, make for longer aliases. Use cust<\/strong> instead of c<\/strong>.<\/li>\n<\/ul>\n
The above should make for more organized and readable SQL code. Remember: what one programmer finds as a very intuitive alias, is unintuitive to another!<\/p>\n
My own convention<\/h4>\n
Simple: I only use aliases<\/em> when using self joins. I am aware that queries are much longer what with long table names. I go farther than that: I prefer fully qualifying questionable columns throughout the query. Yes, it makes the query even longer.<\/p>\nI know this does not appeal to many. But there’s no confusion. And it’s easily searchable. And it’s consistent. And if properly formatted, as in the above queries, is well readable.<\/p>\n
Now please join me in asking Oracle if they can add multi-line Strings for java, as there are for python.<\/p>\n","protected":false},"excerpt":{"rendered":"
After seeing quite some SQL statements over the years, something is bugging me: there is no consistent convention as for how to write an SQL query. I’m going to leave formatting, upper\/lower-case issues aside, and discuss a small part of the SQL syntax: table aliases. Looking at three different queries, I will describe what I […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[6,51,31,21,20],"class_list":["post-2156","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-java","tag-opinions","tag-python","tag-sql","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-yM","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2156","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2156"}],"version-history":[{"count":16,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2156\/revisions"}],"predecessor-version":[{"id":2170,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2156\/revisions\/2170"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2156"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2156"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2156"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}