(window.webpackJsonp=window.webpackJsonp||[]).push([[3246],{3654:function(s,t,a){"use strict";a.r(t);var e=a(31),n=Object(e.a)({},(function(){var s=this,t=s.$createElement,a=s._self._c||t;return a("ContentSlotsDistributor",{attrs:{"slot-key":s.$parent.slotKey}},[a("h1",{attrs:{id:"clean-code-in-sql"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#clean-code-in-sql"}},[s._v("#")]),s._v(" Clean Code in SQL")]),s._v(" "),a("p",[s._v("How to write good, readable SQL queries, and example of good practices.")]),s._v(" "),a("h2",{attrs:{id:"formatting-and-spelling-of-keywords-and-names"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#formatting-and-spelling-of-keywords-and-names"}},[s._v("#")]),s._v(" Formatting and Spelling of Keywords and Names")]),s._v(" "),a("h3",{attrs:{id:"table-column-names"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#table-column-names"}},[s._v("#")]),s._v(" Table/Column Names")]),s._v(" "),a("p",[s._v("Two common ways of formatting table/column names are "),a("a",{attrs:{href:"https://en.wikipedia.org/wiki/Camel_case",target:"_blank",rel:"noopener noreferrer"}},[a("code",[s._v("CamelCase")]),a("OutboundLink")],1),s._v(" and "),a("a",{attrs:{href:"https://en.wikipedia.org/wiki/Snake_case",target:"_blank",rel:"noopener noreferrer"}},[a("code",[s._v("snake_case")]),a("OutboundLink")],1),s._v(":")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" FirstName"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" LastName\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Employees\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" Salary "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("500")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" first_name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" last_name\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" employees\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" salary "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("500")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("Names should describe what is stored in their object. This implies that column names usually should be singular.\nWhether table names should use singular or plural is a "),a("a",{attrs:{href:"http://stackoverflow.com/questions/338156/table-naming-dilemma-singular-vs-plural-names",target:"_blank",rel:"noopener noreferrer"}},[s._v("heavily discussed"),a("OutboundLink")],1),s._v(" question, but in practice, it is more common to use plural table names.")]),s._v(" "),a("p",[s._v("Adding prefixes or suffixes like "),a("code",[s._v("tbl")]),s._v(" or "),a("code",[s._v("col")]),s._v(" reduces readability, so avoid them.\nHowever, they are sometimes used to avoid conflicts with SQL keywords, and often used with triggers and indexes (whose names are usually not mentioned in queries).")]),s._v(" "),a("h3",{attrs:{id:"keywords"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#keywords"}},[s._v("#")]),s._v(" Keywords")]),s._v(" "),a("p",[s._v("SQL keywords are not case sensitive.\nHowever, it is common practice to write them in upper case.")]),s._v(" "),a("h2",{attrs:{id:"indenting"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#indenting"}},[s._v("#")]),s._v(" Indenting")]),s._v(" "),a("p",[s._v("There is no widely accepted standard. What everyone agrees on is that squeezing everything into a single line is bad:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" Employees "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Departments "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" d "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("JOIN")]),s._v(" Employees "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" e "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("ID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("DepartmentID "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("!=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[s._v("'HR'")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("HAVING")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("10")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ORDER")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BY")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DESC")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("At the minimum, put every clause into a new line, and split lines if they would become too long otherwise:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n       "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" Employees\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Departments "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" d\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("JOIN")]),s._v(" Employees "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" e "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("ID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("DepartmentID\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("!=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[s._v("'HR'")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("HAVING")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("10")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ORDER")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BY")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DESC")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("Sometimes, everything after the SQL keyword introducing a clause is indented to the same column:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v("   d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n         "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" Employees\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v("     Departments "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" d\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("JOIN")]),s._v("     Employees "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" e "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("ID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("DepartmentID\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v("    d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("!=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[s._v("'HR'")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("HAVING")]),s._v("   "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("10")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ORDER")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BY")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DESC")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("(This can also be done while aligning the SQL keywords right.)")]),s._v(" "),a("p",[s._v("Another common style is to put important keywords on their own lines:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v("\n    d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n    "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" Employees\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v("\n    Departments "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" d\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("JOIN")]),s._v("\n    Employees "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" e\n    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("ID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("DepartmentID\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v("\n    d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("!=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[s._v("'HR'")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("HAVING")]),s._v("\n    "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("10")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ORDER")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("BY")]),s._v("\n    "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("DESC")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("Vertically aligning multiple similar expressions improves readability:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" Model"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n       EmployeeID\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Cars\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" CustomerID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[s._v("42")]),s._v("\n  "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("AND")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("Status")]),s._v("     "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[s._v("'READY'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("Using multiple lines makes it harder to embed SQL commands into other programming languages.\nHowever, many languages have a mechanism for multi-line strings, e.g., "),a("code",[s._v('@"..."')]),s._v(" in C#, "),a("code",[s._v('"""..."""')]),s._v(" in Python, or "),a("code",[s._v('R"(...)"')]),s._v(" in C++.")]),s._v(" "),a("h2",{attrs:{id:"select"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#select"}},[s._v("#")]),s._v(" SELECT *")]),s._v(" "),a("p",[a("code",[s._v("SELECT *")]),s._v(" returns all columns in the same order as they are defined in the table.")]),s._v(" "),a("p",[s._v("When using "),a("code",[s._v("SELECT *")]),s._v(", the data returned by a query can change whenever the table definition changes. This increases the risk that different versions of your application or your database are incompatible with each other.")]),s._v(" "),a("p",[s._v("Furthermore, reading more columns than necessary can increase the amount of disk and network I/O.")]),s._v(" "),a("p",[s._v("So you should always explicitly specify the column(s) you actually want to retrieve:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token comment"}},[s._v("--SELECT *                                 don't")]),s._v("\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" ID"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" FName"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" LName"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v(" PhoneNumber  "),a("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- do")]),s._v("\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Emplopees"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("(When doing interactive queries, these considerations do not apply.)")]),s._v(" "),a("p",[s._v("However, "),a("code",[s._v("SELECT *")]),s._v(" does not hurt in the subquery of an EXISTS operator, because EXISTS ignores the actual data anyway (it checks only if at least one row has been found). For the same reason, it is not meaningful to list any specific column(s) for EXISTS, so "),a("code",[s._v("SELECT *")]),s._v(" actually makes more sense:")]),s._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token comment"}},[s._v("-- list departments where nobody was hired recently")]),s._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" ID"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n       Name\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Departments\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("NOT")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("EXISTS")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),s._v("\n                  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v(" Employees\n                  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("WHERE")]),s._v(" DepartmentID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" Departments"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("ID\n                    "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("AND")]),s._v(" HireDate "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v(">=")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[s._v("'2015-01-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("h2",{attrs:{id:"joins"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#joins"}},[s._v("#")]),s._v(" Joins")]),s._v(" "),a("p",[s._v("Explicit joins should always be used; "),a("a",{attrs:{href:"http://stackoverflow.com/documentation/sql/261/join/938/implicit-join",target:"_blank",rel:"noopener noreferrer"}},[s._v("implicit joins"),a("OutboundLink")],1),s._v(" have several problems:")]),s._v(" "),a("li",[s._v("\nThe join condition is somewhere in the WHERE clause, mixed up with any other filter conditions. This makes it harder to see which tables are joined, and how.\n")]),s._v(" "),a("li",[s._v("\nDue to the above, there is a higher risk of mistakes, and it is more likely that they are found later.\n")]),s._v(" "),a("li",[s._v("\nIn standard SQL, explicit joins are the only way to use [outer joins](http://stackoverflow.com/documentation/sql/261/join/14930/differences-between-inner-outer-joins):\n"),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n       e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Fname "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("||")]),s._v(" e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("LName "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" EmpName\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v("      Departments "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" d\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("LEFT")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("JOIN")]),s._v(" Employees   "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" e "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("ON")]),s._v(" d"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("ID "),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("=")]),s._v(" e"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("DepartmentID"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])])]),s._v(" "),a("li",[s._v("\nExplicit joins allow using the USING clause:\n"),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("SELECT")]),s._v(" RecipeID"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n       Recipes"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(".")]),s._v("Name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(",")]),s._v("\n       "),a("span",{pre:!0,attrs:{class:"token function"}},[s._v("COUNT")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),a("span",{pre:!0,attrs:{class:"token operator"}},[s._v("*")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("AS")]),s._v(" NumberOfIngredients\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("FROM")]),s._v("      Recipes\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("LEFT")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("JOIN")]),s._v(" Ingredients "),a("span",{pre:!0,attrs:{class:"token keyword"}},[s._v("USING")]),s._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v("(")]),s._v("RecipeID"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[s._v(";")]),s._v("\n\n")])])]),a("p",[s._v("(This requires that both tables use the same column name."),a("br"),s._v("\nUSING automatically removes the duplicate column from the result, e.g., the join in this query returns a single `RecipeID` column.)")])])])}),[],!1,null,null,null);t.default=n.exports}}]);