Jun 24, 2014 on SQL
Writing in SQL can be daunting at first, but the more you practice the more it seems like it’s its own programming language. For example, the
SELECT statement can be confusing as to what it actually does but when you realize it is the equivalent of the return statement in many languages it started to make sense. Another thing to worry about is the columns of the table I like to think of it as a hash or dictionary, where the key is the column name and the value is what is in each row’s corresponding column.
One main problem I kept running into was using
JOIN tables. I finally realized that the table with the foreign key is the table that gets joined to the main table.
SELECT galleries.name, images.name
JOIN images ON galleries.id = images.gallery_id;
This would return a seemingly new table with the gallery name linked to its corresponding image name. You can further refine the results using
WHERE to enter a conditional and
ILIKE to search text similar to a regular expression.
SUM which can return the sum of the column, or
COUNT which can return how many of a particular query would be returned. One problem with these functions is if you want to return at least an additional column along with the aggregate function you must use the
GROUP BY clause. Also, if you are returning multiple columns you must specify it in the group clause when using aggregate functions.
The most difficult problem I ran into was finding the first unique name in a join table. To do this you must use the
SELECT DISTINCT ON(x) statement, where x is the distinct element that you want. One note with this is your
ORDER BY argument must match the distinct element that you passed in.