SQL Refresher For ORM Users!
If you’re like me and have used an ORM (like Active Record) for your back end API endpoints, there may be a good chance that you have found yourself to be slipping when it comes to your knowledge of basic SQL principles and query methods!
It can be easy to fall into the comfort and ease of use of things like Active Record as they kind of remove some of the verbose elements of SQL queries and database relationships have already been defined in other areas of your back end code.
Something like finding a user and updating their username with Active Record like so:
user = User.find_by(username: ‘Joe’)user.update(username: ‘Bob’)
Turns into:
UPDATE users
SET username = 'Bob'
WHERE username = 'Joe'And that is just a very simple example! Things get much more complicated when it comes to SQL GROUP BY, JOINS, and aggregate functions like SUM and COUNT.
So, let’s talk through a quick and simple SQL refresher!
These are the following topics I found myself most lacking in so I will go through them in this post:
- What is the basic SQL query hierarchy?
- What is the difference between the use of ORDER BY and GROUP BY?
- What is the difference between the use of WHERE vs. HAVING clauses?
- What are the different SQL JOIN clauses?
- What is a Common Table Expression?
What is the basic SQL query hierarchy?
Now obviously not every query will contain all of these clauses, however I think it is important to have knowledge of what clauses should be going where in a query. Having this basic skeleton down will be very helpful when it comes to writing more complex queries!
SELECT (column or aggregate function)FROM (desired table)JOIN (if foreign key exists, put the table with the relation to ON (where ID from table in FROM clause = foreign key value from table in JOIN clause - Ex: ON users.id = posts.user_id)WHERE GROUP BY HAVINGORDER BY
What is the difference between the use of ORDER BY and GROUP BY?
This is a pretty easy one!
ORDER BY() sorts the resulting data set of basic queries while GROUP BY() sorts the result sets of aggregate functions. ORDER BY() works on individual rows while GROUP BY() works on groups of rows.
What is the difference between the use of WHERE vs. HAVING clauses?
The WHERE clause does not work on aggregate functions. The WHERE clause works on a row’s data. HAVING does work on aggregates and filters out rows created by a GROUP BY. In other words, HAVING works on groups of rows, and WHERE works on each row individually.
So, if you have a query that sums up all the times of type “book”,
SELECT COUNT(items.type)
FROM items
GROUP BY items.type
WHERE items.type = 'book"would not work and would return a syntax error. You would instead use a HAVE in place of the WHERE in the above example.
What are the different SQL JOIN clauses?
For visualization purposes, let’s use a venn diagram to help keep these straight.
- Inner Join — Returns all rows when there is at least one match in BOTH tables. This can be compared to the middle section of the venn diagram, where there are only items that BOTH sides share.
- Left Outer — Returns all rows from the left table, and the matched rows from the right table. This can be compared to the middle and left side of a venn diagram where you get items that both sides share AS WELL as items that are exclusive to the left side (hence “left” join).
- Right Outer — Returns all rows from the right table, and the matched rows from the left table. This can be compared to the middle and right side of a venn diagram where you get items that both sides share AS WELL as items that are exclusive to the right side (hence “right” join).
- Full Join — returns all of the records from both tables, joining records from the left table that match records from the right table. This can be compared to returning the entire venn diagram including the middle section.
What is a Common Table Expression?
A common table expression (or CTE) allows you to create a temporary named result set and can be used within the context of a larger query. The result set of a CTE is not stored and only exists for the duration of the query.
Here is a short example using an imaginary table that stores user information including a column containing the user’s role:
WITH administrators AS (
SELECT * FROM users
WHERE users.role = 'administrator'
) <--- CTE Creation
SELECT * FROM administrators <--- CTE usage
WHERE...Why Use a CTE?
Like helper methods, CTE’s help to increase the readability and can work to simplify more complex queries. CTE’s can also be used to avoid having to create a new view in the database!
That’s it for now! I hope I was able to help you to refresh some basic SQL concepts in this blog! All in all I don’t think it is too tough to relearn this stuff and what will end up helping you the most is to practice the syntax on sites like LeetCode that have SQL challenge questions!
