Now that we have acquainted ourselves with the very basics of SQL, let’s move on to the more interesting stuff. If you haven’t read the previous blogs, you may find them useful, in case you are completely new to SQL.

Lets start!

The GROUP BY

This is a simple way to see how data is distributed based on the selected categories. So, in essence, it allows us to aggregate data. For example, let’s say you have a database of animals who are ready to be adopted (This time I won’t be biased toward dogs). There are 4 categories: Dogs, Cats, Unicorns, and Parrots. Each category has different breeds of that animal with the total number of them available for adoption.

Now you want to group them by category (Unicorn and dogs) and want to see how many of them are up for adoption in each category. GROUP BY are extremely useful in such scenarios where you apply a condition to a category.

SELECT unicorn, dog SUM(number) FROM animal_table
GROUP BY unicorn, dog;

Whatever you mention in the SELECT statement, you have to mention in the GROUP BY statement. Here you mentioned unicorn and dog, you have to mention it in the GROUP BY statement as well, except for the aggregate terms like SUM, AVG, MIN, etc. You always use ORDER BY after GROUP BY. And you use WHERE before GROUP BY

The HAVING Call

This is used to apply some filtering after the GROUP BY. For example, you want to apply a filter such that, the sum of animals in a category is greater than 40. This will only return the category Cat since it has 50 in number for adoption.

SELECT animals, SUM(number) FROM animal_table
GROUP BY animals
HAVING SUM(number) > 40;

The INNER JOIN

Allows us to join values that match in multiple tables. Consider the following figure of 2 tables bob and nancy, and you want to see which entries in both tables match, do as follows:

SELECT * FROM bob
INNER JOIN nancy
ON bob.pets = nancy.pets;

The FULL OUTER JOIN

Allows us to join values that are present in multiple tables. Consider the following figure of 2 tables bob and nancy, and you want to see all entries in both tables in the result, do as follows:

SELECT * FROM bob
FULL OUTER JOIN nancy
ON bob.pets = nancy.pets;

To get only the unique values from either tables and not the column one, you do a FULL JOIN first and then add in a WHERE statement as below:

SELECT * FROM bob
FULL OUTER JOIN nancy
ON bob.pets = nancy.pets
WHERE bob.pets IS null or nancy.pets IS null;

The LEFT OUTER JOIN

This helps us to get those rows that are present in the left_table or in both left_table and right_table and nothing that is present in just the right_table. The result will have the following highlighted entries.

SELECT * FROM bob
LEFT OUTER JOIN nancy
ON bob.pets = nancy.pets;

Key points to remember: The order of the tables mentioned in the query above matters unlike the FULL OUTER JOIN and INNER JOIN. Here the left table will be bob since you mentioned it first.

If you want to get rows exclusive to the left table and doesn’t want anything common between left and right, add in a WHERE statement as follows:

SELECT * FROM bob
LEFT OUTER JOIN nancy
ON bob.pets = nancy.pets
WHERE nancy.pets IS null;

The result will have the following entries:

The RIGHT OUTER JOIN

This returns rows that are present in the right_table, along with rows present in both left_table and the right_table and nothing that is only present in the left_table.

The following figure shows the result from a RIGHT OUTER JOIN query on bob and nancy.

SELECT * FROM bob
RIGHT OUTER JOIN nancy
ON bob.pets = nancy.pets;

If you want to get rows exclusive to the right table and doesn’t want anything common between left and right, add in a WHERE statement as follows:

SELECT * FROM bob
RIGHT OUTER JOIN nancy
ON bob.pets = nancy.pets
WHERE bob.pets IS null;

The AS statement

AS, and not ass, is a simple way to rename a column in a query result to the name you like. It’s useful if you want to show your query results to someone and want to make it more readable. The AS statement always appears in the SELECT statement and nowhere else. Bear this in mind. The syntax is as follows:

To display the number column from the animal_table as ready for adoption instead, do the following:

SELECT number AS ready_for_adoption FROM animal_table;

The UNION

Used to concatenate or stack up the results from two SELECT statements.

The syntax is as follows:

SELECT * FROM bob
UNION
SELECT * FROM nancy;

Disclaimer: If there are ay mistakes that I missed to address in this blog, kindly forgive and try everything yourself to double check. A good way to learn! More on SQL in the follow up blogs.