It’s time for some jargon-busting.<\/p>\n\n\n\n
A big part of my role is to onboard and support junior data analysts. A lot of them have just started<\/a> using SQL, have come from the world of Excel<\/a> analysis and have self-taught the SQL basics.<\/p>\n\n\n\n
Here are some of those terms and concepts that pop up during training if you need a refresher or have a junior analyst <\/a>in your life that needs something to refer back to.<\/p>\n\n\n\n
Alias<\/a>
Begin Transaction<\/a>
CTEs v Subqueries<\/a>
Design<\/a>
ETL<\/a>
Function<\/a>
Group By<\/a>
Heaped Storage<\/a>
Integrity<\/a>
Join<\/a>
Key<\/a>
Lock<\/a>
Massive Parallel Processing<\/a>
Normalisation<\/a>
OLTP v OLAP<\/a>
Privileges<\/a>
Query Plan<\/a>
Disaster Recovery<\/a>
System Tables<\/a>
Truncate v Drop<\/a>
Union<\/a>
View<\/a>
Window Function<\/a>
XML<\/a>
Year<\/a>
Zero<\/a><\/p><\/blockquote>\n\n\n\n
\n\n\n\nAlias<\/h2>\n\n\n\n
When joining<\/a> tables, we need to state which column<\/a> from which table we want to match up, and which columns we want to return in the results. If there are columns with the same name we need to be specific about which column we want to return.<\/p>\n\n\n\n
select\n orders.item, \n inventory.item,\n inventory.unitprice\nfrom \n orders \ninner join \n inventory \non orders.order_item = inventory.inventory_item\n<\/code><\/pre>\n\n\n\nTo make it quicker to type we can alias the two tables with something shorter.<\/p>\n\n\n\n
select\n o.item, \n i.item,\n i.unitprice\nfrom \n orders o\ninner join \n inventory i \non o.order_item = i.inventory_item\n<\/code><\/pre>\n\n\n\nInstead of having to type out the whole table name each time we want a new column added, we can alias them with the letter ‘o’ for orders and ‘i’ for inventory.<\/p>\n\n\n\n
Read more about JOINs and aliasing in this beginner-friendly post<\/a>.<\/p>\n\n\n\n
\n\n\n\nBegin Transaction<\/h2>\n\n\n\n
SQL Transactions<\/a> are used to trap errors when making changes to tables. During an UPDATE or DELETE statement, the change is auto-committed.<\/p>\n\n\n\n
The following transaction will run in a block and commit if successful.<\/p>\n\n\n\n
begin transaction\n\nupdate orders\nset status = 'sent'\nwhere order_id = '12345'\n\nupdate orders\nset status = 'sent'\nwhere order_id = '54321'\n\ncommit transaction\n<\/code><\/pre>\n\n\n\nRead more<\/a> about how these work<\/p>\n\n\n\n
\n\n\n\nCTEs v Subqueries<\/h2>\n\n\n\n
CTEs (Common Table Expressions)<\/a> are a temporary, named result set we can come back to in the scope of a single SELECT, INSERT, UPDATE, DELETE or MERGE statement.<\/p>\n\n\n\n
; -- start the CTE with a semicolon to terminate anything above\n\nwith sent as -- here is where you name the dataset\n\n(select \n emailaddress,\n emailid, \n senddate\nfrom\n marketing.emailsent\nwhere\n senddate between '2018-01-01' and '2018-01-31'\n), -- add a comma if you need to add a subsequent CTE\n\nunsubs as\n\n(select \n emailaddress,\n emailid, \n senddate\nfrom\n marketing.emailunsubscribe\nwhere\n senddate between '2018-01-01' and '2018-01-31'\n) -- no comma for the last CTE\n\nselect\n 'January' as [monthdelivered],\n c.country, \n count(distinct sent.emailaddress) as [countofdelivered], \n count(distinct unsubs.emailaddress) as [countofunsubd]\nfrom sent\nleft join \n marketing.customers c on sent.email = unsubs.emailaddress\nleft join \n unsubs on sent.email = unsubs.email \n and sent.emailid = unsubs.emailid\n<\/code><\/pre>\n\n\n\nChidiebere<\/a> has written an excellent series<\/a> on CTEs and how they compare with the subquery.<\/p>\n\n\n\n
\n\n\n\nDesign<\/h2>\n\n\n\n
Datamarts tables<\/a> are organised in one of two forms. A \u2018Star\u2019 schema and a \u2018Snowflake\u2019 schema made of two types of tables.<\/p>\n\n\n\n
- Facts<\/strong> – that count how many times something has happened.<\/li>
- Dimensions<\/strong> – (or Dims) that describe an attribute.<\/li><\/ul>\n\n\n\n
In the Star model, we can have a sales table as our Fact in the centre. Dimension tables for the store, product and location surround the Fact like a star.<\/p>\n\n\n\n
<\/a> Attribution: SqlPac at English Wikipedia<\/em><\/figcaption><\/figure>\n\n\n\n <\/p>\n\n\n\n
The Snowflake is similar but takes the Dimensions one step further. Instead of just a location table, we may have a city, country and even a postcode table. All the Dimensions become the points on the snowflake.<\/p>\n\n\n\n
<\/a> Attribution: SqlPac at English Wikipedia<\/em><\/figcaption><\/figure>\n\n\n\n Read more<\/a> about the advantages and disadvantages of both.<\/p>\n\n\n\n
\n\n\n\nETL<\/h2>\n\n\n\n
ETL and ELT are the steps involved in moving data from a source system to a destination system.<\/p>\n\n\n\n
- Extract<\/strong> – in the Extract step the raw data is moved from source to a temporary or staging area.<\/li>
- Transform<\/strong> – the Transform step converts the data so matches the destination table.<\/li>
- Load<\/strong> – the Load step moves the data into its final destination so it can be used in analysis or reporting.<\/li><\/ul>\n\n\n\n
ETL is the order that these steps are traditionally performed in. ETL is great for putting data into the right format, stripping out unnecessary columns, and masking fields relating to GDPR compliance.<\/p>\n\n\n\n
However, ELT has become a more popular approach when used in conjunction with Data Lake architecture. The data arrives quickly as it does not have to be altered in any way. The Data Scientist<\/a> can then use just the data they need, quickly get results, and not have to deal with delays if a transformation step fails.<\/p>\n\n\n\n
For more on ETL, ELT and data pipelines check out this post<\/a> from SeattleDataGuy<\/a> who writes excellent posts on all things data.<\/p>\n\n\n\n
\n\n\n\nFunction<\/h2>\n\n\n\n
In PostgreSQL, we can execute blocks of code, called Functions, on a schedule. They can be written like the statements we run ad hoc on the database or can be parsed variables to make them dynamic.<\/p>\n\n\n\n
Read more<\/a> about how to write and execute functions<\/p>\n\n\n\n
\n\n\n\nGroup By<\/h2>\n\n\n\n
Aggregate functions<\/a> allow us to perform calculations on fields. The most common ones are SUM, COUNT, MIN, MAX, AVERAGE.<\/p>\n\n\n\n
select \n order_item, \n sum(amount_due) \nfrom orders\ngroup by order_item;\n<\/code><\/pre>\n\n\n\nRead more<\/a> about Aggregate functions<\/p>\n\n\n\n
\n\n\n\nHeaped Storage<\/h2>\n\n\n\n
Heaped Storage is a term for tables that live on the database with no clustered index. The data is stored in no particular order and new data simply gets added as it comes in.<\/p>\n\n\n\n
Indexes<\/a> are a way of telling the database to order the data or where to look to find the data you query often.<\/p>\n\n\n\n
- Clustered Indexes<\/strong> are like the contents page of a book. Applying this kind of index is telling the data how it should be ordered, like the pages in a book.<\/li><\/ul>\n\n\n\n
<\/a> Clustered Index<\/figcaption><\/figure><\/div>\n\n\n\n
- Non-clustered Indexes<\/strong> are like the index of a book, the pages haven’t been arranged that way physically, but you now have a lookup to get to what you need faster.<\/li><\/ul>\n\n\n\n
<\/a> Non Clustered Index<\/figcaption><\/figure><\/div>\n\n\n\n Read more<\/a> in this beginner-friendly post.<\/p>\n\n\n\n
\n\n\n\nIntegrity<\/h2>\n\n\n\n
This refers to data quality and rules ensuring data is traceable, searchable and recoverable.<\/p>\n\n\n\n
- Entity Integrity<\/strong> – each table must have a unique primary key<\/li>
- Referential Integrity<\/strong> – foreign keys on each table refers to a primary key on another or is NULL<\/li>
- Domain Integrity<\/strong> – each column has a specific data type and length.<\/li><\/ul>\n\n\n\n
Read more<\/a> about Integrity and Database design.<\/p>\n\n\n\n
\n\n\n\nJoin<\/h2>\n\n\n\n
Because our database contains tables which are normalised<\/a> you may not find all the data you need on one single table. To put the data back together in a way that makes the most sense for us we use JOINs<\/a>. This adds columns from multiple tables into one dataset.<\/p>\n\n\n\n
<\/a> Inner Join<\/figcaption><\/figure><\/div>\n\n\n\n Use a LEFT JOIN when you want to find a match between two tables, but also show a NULL where there is no match from the right table. A RIGHT JOIN does the same but in reverse.<\/p>\n\n\n\n
Like the INNER JOIN, you need a column to join ON. Unlike the INNER JOIN, a NULL is used to show there is no match between the two tables.<\/p>\n\n\n\n
<\/a> Left Join<\/figcaption><\/figure><\/div>\n\n\n\n Katie has written a great post on ‘Every JOIN you will ever need’<\/a> with a focus on Oracle syntax.<\/p>\n\n\n\n
\n\n\n\nKey<\/h2>\n\n\n\n
A primary key is a column that best identifies one unique row, and identifies each record as unique, like an ID<\/p>\n\n\n\n
- It ensures that there are no duplicates<\/li>
- It cannot be unknown (NULL)<\/li>
- There can only be one primary key per table<\/li><\/ul>\n\n\n\n
A foreign key is a column that matches a primary key in another table and enforces integrity between the two.<\/p>\n\n\n\n
To create a primary key in SQL Server<\/a> add the reserved words ‘primary key’ after the data type of your chosen column.<\/p>\n\n\n\n
create table students (\n id int not null primary key,\n firstname varchar(255) not null,\n lastname varchar(255) not null,\n);\n<\/code><\/pre>\n\n\n\nLenique puts this into practice with this post on relational model design.<\/a><\/p>\n\n\n\n
\n\n\n\nLock<\/h2>\n\n\n\n
When two users are trying to query or update the same table at the same time it may result in a lock. In the same way that two people with ATM cards for the same bank account are trying to withdraw the same $100 from the same bank account, one will be locked out while the first transaction is completed.<\/p>\n\n\n\n
Rhymes<\/a> does a great job of explaining how it works on the database:<\/p>\n\n\n\n
\n\n\n\nMassive Parallel Processing<\/h2>\n\n\n\n
In Massively Parallel Processing databases, like Redshift, data is partitioned across multiple compute nodes with each node having memory to process data locally.<\/p>\n\n\n\n
Redshift distributes the rows of a table to the nodes so that the data can be processed in parallel. By selecting an appropriate distribution key for each table, the workload can be balanced.<\/p>\n\n\n\n
Read more about Redshift with ronsoak<\/a> who has written a complete guide to Redshift.<\/p>\n\n\n\n
\n\n\n\nNormalisation<\/h2>\n\n\n\n
Database normalisation increases data integrity and allows new data to be added without changing the underlying structure.<\/p>\n\n\n\n
- Eliminate or minimise duplication<\/strong> – repeating a value across multiple tables means that tables take up more space than needed which increases storage costs. Storing customers address details on one table with keys linking to their orders will take up less space than repeating address details on each row of the order table.<\/li>
- Simplify updates to data<\/strong> – by keeping a value in one table with a key to another we minimise the risk of errors when there are updates to be made. If there are two places where a customers email is stored and only one gets updated there will be confusion over which one is correct.<\/li>
- Simplify queries<\/strong> – searching and sorting become easier if there is no duplication on tables.<\/li><\/ul>\n\n\n\n
Read more<\/a> about Normalisation<\/p>\n\n\n\n
\n\n\n\nOLTP v OLAP<\/h2>\n\n\n\n
OLTP and OLAP refer to different types of databases and tools that perform different functions.<\/p>\n\n\n\n
- OLTP<\/strong> – Online Transaction Processing – used for fast data processing and responds immediately to queries.<\/li>
- OLAP<\/strong> – Online Analytics Processing – used for storing historical data and data mining.<\/li><\/ul>\n\n\n\n
Read more<\/a> about OLTP<\/p>\n\n\n\n
\n\n\n\nPrivileges<\/h2>\n\n\n\n
If you intend on sharing a table with your colleagues who have access to your schema, you need to explicitly grant access to them. This keeps data locked down to just those who need to see it.<\/p>\n\n\n\n
GRANT ALL ON <schemaname.tablename> TO <username> \n-- if you would like them to SELECT, UPDATE and DELETE\n\nGRANT SELECT ON <schemaname.tablename> TO <username> \n-- if you would like them to be able to only SELECT\n<\/code><\/pre>\n\n\n\nRead more<\/a> about permissions and everything else you need to know about databases.<\/p>\n\n\n\n
\n\n\n\nQuery Plan<\/h2>\n\n\n\n
When we run a query there are many things that the SQL engine considers – the joins, the indexes, whether it will scan through the whole table or be faced with table locking.<\/p>\n\n\n\n
In SQL Server we can use the Execution Plan<\/a> to visualise runtime information and any warnings.<\/p>\n\n\n\n
<\/a> From the SQL Server documentation<\/em><\/figcaption><\/figure>\n\n\n\n In PostgreSQL we can check the query plan using the EXPLAIN command:<\/p>\n\n\n\n
EXPLAIN -- show the execution plan of a statement\nEXPLAIN ANALYZE -- causes the query to be executed as well explain\n<\/code><\/pre>\n\n\n\nRead more<\/a> about what each query looks like and how to interpret the results.<\/p>\n\n\n\n
\n\n\n\nRecovery<\/h2>\n\n\n\n
Disaster Recovery in the database world relates to the backups, logs and replication instances that are maintained while everything is working fine. These can then be switched on, switched over and analysed when something does go wrong, like a hardware failure, natural disaster or even human error.<\/p>\n\n\n\n
- Failover<\/strong> – multiple clusters are set up so if one fails the other can take over.<\/li>
- Mirroring<\/strong> – maintaining two copies of the same database at different locations. One in offline mode so we know where things are at when we need to use it.<\/li>
- Replication<\/strong> – the secondary database is online and can be queried. This is not only good for Disaster Recovery but can be useful if you utilise one instance for reporting and one for live queries. If you are using AWS setting this up takes just a few clicks.<\/li><\/ul>\n\n\n\n
\n\n\n\nSystem Tables<\/h2>\n\n\n\n
In SQL Server these are often referred to as system tables and views. They can be found in the master database, which holds data about the database<\/a>. And in the system views within each database for specific information about each database.<\/p>\n\n\n\n
Examples of system views:<\/p>\n\n\n\n
- sys.objects – shows each object, its type and created date<\/li>
- sys.indexes – shows each index and type<\/li>
- information_schema.columns – shows each column, it’s position and datatype<\/li><\/ul>\n\n\n\n
Examples of catalog objects:<\/p>\n\n\n\n
- information_schema.tables – shows each object, its type and created date<\/li>
- pg_index – shows each index and type<\/li>
- information_schema.columns – shows each column, it’s position and datatype.<\/li><\/ul>\n\n\n\n
\n\n\n\nTruncate v Drop<\/h2>\n\n\n\n
Both of these commands will remove the data from a table but in different ways.<\/p>\n\n\n\n
- TRUNCATE<\/strong> is a DDL command which removes the contents of the table while leaving the structure in place<\/li><\/ul>\n\n\n\n
truncate table marketing.emailcampaign\n<\/code><\/pre>\n\n\n\n
- DELETE<\/strong> is a DML command which removes rows given a WHERE clause<\/li><\/ul>\n\n\n\n
delete from \n marketing.emailcampaign\nwhere\n month = 'January'\n<\/code><\/pre>\n\n\n\nFor a break down of which commands fall into DDL and which are DML commands check out this post.<\/a><\/p>\n\n\n\n
\n\n\n\nUnion<\/h2>\n\n\n\n
While a JOIN combines rows of columns horizontally, a UNION combines the results vertically. Using a UNION combines the result of two queries into one column and removes duplicates. If your query has multiple columns, they need to be in the same order to complete the UNION.<\/p>\n\n\n\n
<\/a> Union<\/figcaption><\/figure><\/div>\n\n\n\n select *\nfrom\n orders\nunion\nselect *\nfrom\n inventory\n<\/code><\/pre>\n\n\n\nThe UNION ALL combines the results of two queries the same as a UNION but keeps the duplicates in the result.<\/p>\n\n\n\n
<\/a> Union All<\/figcaption><\/figure><\/div>\n\n\n\n select *\nfrom\n orders\nunion all\nselect *\nfrom\n inventory\n<\/code><\/pre>\n\n\n\n
\n\n\n\nView<\/h2>\n\n\n\n
Views are not tables, they are queries that are executed on the fly and are used as a way to create a level of abstraction from the base table.<\/p>\n\n\n\n
Joe sums it up perfectly in his post<\/a><\/p>\n\n\n\n
\n\n\n\nWindow Function<\/h2>\n\n\n\n
A window function<\/a> gets its name because, unlike an aggregate function<\/a>, it keeps each row intact and adds a row number or running total.<\/p>\n\n\n\n
Here is an example using the orders table that returns a rank using the order_value.<\/p>\n\n\n\n
select\n order_id,\n order_name\n order_date,\n rank() over(order amount_due desc)\nfrom \n dbo.orders\n<\/code><\/pre>\n\n\n\n
\n\n\n\nXML<\/h2>\n\n\n\n
We can import files into tables using the import\/export wizard<\/a>. But they don’t have to just be csv or txt files. By using a few lines of code we can import XML<\/a> as well.<\/p>\n\n\n\n
Read more<\/a> with instructions<\/p>\n\n\n\n
\n\n\n\nYear<\/h2>\n\n\n\n
Depending on your flavour of SQL you will be able to calculate the difference between two dates and compare a date with the current date. There is added complexity when moving between databases<\/a> so keep this in mind when on your next migration.<\/p>\n\n\n\n
\n\n\n\nZero<\/h2>\n\n\n\n
NULL means that the value is unknown, not zero and not blank. This makes it difficult to compare values if you are comparing NULLs with NULLs.<\/p>\n\n\n\n
Because NULL is not a value it isn’t possible to use comparison operators. Instead, we need to use the IS, and IS NOT operators:<\/p>\n\n\n\n
select * \nfrom \n inventory\nwhere \n unitprice is not null\n<\/code><\/pre>\n\n\n\nBy default, NULLs will appear as the largest value and making sorting at best annoying and at worse misleading. To get around this we can use COALESCE to treat NULLs as a 0<\/a>.<\/p>\n\n\n\n
select \n itemname, \n coalesce(unitprice, 0)\nfrom \n inventory\norder by 2\n<\/code><\/pre>\n\n\n\n
\n\n\n\nThere we have it, a quick introduction to the key terms, concepts and jargon for those new to the world of SQL and databases.<\/p>\n\n\n\n
\n\n\n\nPhoto by Pixabay on Pexels<\/p>\n","protected":false},"excerpt":{"rendered":"
This post covers the SQL concepts that pop up during training. If you need a refresher or have a junior analyst in your life who needs a resource to refer to, keep reading.<\/p>\n","protected":false},"author":1,"featured_media":3057,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":""},"categories":[214],"tags":[198,199],"yoast_head":"\n
SQL concepts from A to Z | Helen Anderson<\/title>\n<meta name=\"description\" content=\"This post covers the SQL concepts that pop up during training. If you need a refresher or have a junior analyst in your life who needs a resource to refer to, keep reading.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL concepts from A to Z | Helen Anderson\" \/>\n<meta property=\"og:description\" content=\"This post covers the SQL concepts that pop up during training. If you need a refresher or have a junior analyst in your life who needs a resource to refer to, keep reading.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/\" \/>\n<meta property=\"og:site_name\" content=\"Helen Anderson\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-10T07:16:10+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-08-24T10:07:07+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2020\/05\/sql-a-to-z.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"853\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:site\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"15 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/13.237.200.153\/#website\",\"url\":\"https:\/\/13.237.200.153\/\",\"name\":\"Helen Anderson\",\"description\":\"Data Analyst | Technical Writer\",\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/13.237.200.153\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-NZ\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#primaryimage\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2020\/05\/sql-a-to-z.jpg\",\"width\":1280,\"height\":853,\"caption\":\"sql a to z\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#webpage\",\"url\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/\",\"name\":\"SQL concepts from A to Z | Helen Anderson\",\"isPartOf\":{\"@id\":\"https:\/\/13.237.200.153\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#primaryimage\"},\"datePublished\":\"2019-02-10T07:16:10+00:00\",\"dateModified\":\"2020-08-24T10:07:07+00:00\",\"description\":\"This post covers the SQL concepts that pop up during training. If you need a refresher or have a junior analyst in your life who needs a resource to refer to, keep reading.\",\"inLanguage\":\"en-NZ\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/\"]}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#webpage\"},\"author\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"headline\":\"SQL concepts from A to Z\",\"datePublished\":\"2019-02-10T07:16:10+00:00\",\"dateModified\":\"2020-08-24T10:07:07+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#webpage\"},\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"image\":{\"@id\":\"https:\/\/www.helenanderson.co.nz\/sql-concepts-from-a-to-z\/#primaryimage\"},\"keywords\":\"analysis,sql\",\"articleSection\":\"Data Analysis\",\"inLanguage\":\"en-NZ\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\",\"name\":\"Helen Anderson\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/13.237.200.153\/#personlogo\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2019\/11\/helen-anderson-profile-selects-FA-1000.jpg\",\"width\":1000,\"height\":1000,\"caption\":\"Helen Anderson\"},\"logo\":{\"@id\":\"https:\/\/13.237.200.153\/#personlogo\"},\"description\":\"Hi, I'm Helen. I'm a data analyst, technical writer, and AWS Data Hero. I interpret the story behind the numbers, build data applications, and grow analyst and developer communities - currently at Kiwibank.\",\"sameAs\":[\"http:\/\/www.helenanderson.co.nz\/\",\"https:\/\/www.instagram.com\/helenanders26\/\",\"https:\/\/www.linkedin.com\/in\/helenanders26\/\",\"https:\/\/twitter.com\/helenanders26\",\"https:\/\/www.youtube.com\/channel\/UCttVhJizwkhgmMlDBMUE0wQ\"]}]}<\/script>\n","_links":{"self":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/3056"}],"collection":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/comments?post=3056"}],"version-history":[{"count":7,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/3056\/revisions"}],"predecessor-version":[{"id":3479,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/3056\/revisions\/3479"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media\/3057"}],"wp:attachment":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media?parent=3056"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/categories?post=3056"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/tags?post=3056"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![<\/a>Attribution: SqlPac at English Wikipedia<\/em><\/figcaption><\/figure>\n\n\n\n<\/p>\n\n\n\nThe Snowflake is similar but takes the Dimensions one step further. Instead of just a location table, we may have a city, country and even a postcode table. All the Dimensions become the points on the snowflake.<\/p>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--Ue0J4VG2--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/in90kvm3zj8rm9sbuafw.png\"){kind=link}
![<\/a>Attribution: SqlPac at English Wikipedia<\/em><\/figcaption><\/figure>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--tLvBWeFe--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/pqjymxanga0jkl2qlt62.png\"){kind=link}
![<\/a>Clustered Index<\/figcaption><\/figure><\/div>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--SzOdC3Hp--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/thepracticaldev.s3.amazonaws.com\/i\/y7yo2tivweq1dsu763ie.PNG\"){kind=link}
![<\/a>Non Clustered Index<\/figcaption><\/figure><\/div>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--lBIoytMy--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/thepracticaldev.s3.amazonaws.com\/i\/cn0p49zsmec3x1zenlf5.PNG\"){kind=link}
![<\/a>Inner Join<\/figcaption><\/figure><\/div>\n\n\n\nUse a LEFT JOIN when you want to find a match between two tables, but also show a NULL where there is no match from the right table. A RIGHT JOIN does the same but in reverse.<\/p>\n\n\n\nLike the INNER JOIN, you need a column to join ON. Unlike the INNER JOIN, a NULL is used to show there is no match between the two tables.<\/p>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--RJnMgyP0--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/thepracticaldev.s3.amazonaws.com\/i\/4awenpvfcedaz8t7ldx8.PNG\"){kind=link}
![<\/a>Left Join<\/figcaption><\/figure><\/div>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--qjtkFpj0--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/thepracticaldev.s3.amazonaws.com\/i\/rnhge20cokfe29jol171.PNG\"){kind=link}
![<\/a>From the SQL Server documentation<\/em><\/figcaption><\/figure>\n\n\n\nIn PostgreSQL we can check the query plan using the EXPLAIN command:<\/p>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--keCL0AKJ--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/dev-to-uploads.s3.amazonaws.com\/i\/22mek5y6qjzfp75qbo77.png\"){kind=link}
![<\/a>Union<\/figcaption><\/figure><\/div>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--eEMklGVI--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/thepracticaldev.s3.amazonaws.com\/i\/76x161yx9qx01nw5wihc.PNG\"){kind=link}
![<\/a>Union All<\/figcaption><\/figure><\/div>\n\n\n\n](\"https:\/\/res.cloudinary.com\/practicaldev\/image\/fetch\/s--ewN3fWwl--\/c_limit%2Cf_auto%2Cfl_progressive%2Cq_auto%2Cw_880\/https:\/\/thepracticaldev.s3.amazonaws.com\/i\/eypfji1dzlvquhhaccn0.PNG\"){kind=link}