SQL is a language used for database interaction. Writing SQL at EPIX has shown me that what I was taught at A-Level was, unsurprisingly, useless! Let’s look more deeply at SQL.
SQL stands for Structured Query Language. There are lots of databases that use slightly differing syntax of SQL, some of which include MySQL, Firebird, Azure and Oracle, hence not all SQL will look the same. That said, there are of course the iconic keywords such as select, update, create and delete that all database fans know, however, for a simpleton such as myself the more detailed queries become myth, something to look at but never to touch! This is what I will be researching today, how some of these magical words actually work and looking at some tips for overly efficient queries. Warning! Wordy gibberish ahead…
Joins
Courtesy of Ian, the star of all EPIX queries is the left join, so this is where I have decided to start our journey. Despite being an EPIX developer who rightly writes left joins rather often, I might be the only one left who doesn’t know how they are the right join… let’s do some research!
W3C talks about four different types of SQL joins and I have created a similar diagram below to help visualise this:
Inner join, the query results will only display results where the connecting condition is met in both tables e.g. INNER JOIN order ON customer.order_id = order.id, returns only results where a customer has an order.
In a left join, all results from the main table will be returned as well as any matches from the joined table, e.g. LEFT JOIN order ON customer.order_id = order.id, all customers will be returned as well as any orders they might have, or null if there are no matches.
The right join is of course similar to the left join, although it gives all results from the joined table as well as any matches found in the main table, e.g. RIGHT JOIN order ON customer.order_id = order.id would return all the orders, and give a customer only when there is one related to the order, otherwise returns nothing.
A full join, or full outer join, returns all records from either table regardless of whether they match or not e.g. FULL JOIN order ON customer.order_id = order.id will provide all customers and all orders. This join in particular is written in extremely different ways.
Another interesting concept is the self join ; a table is joined to another version of itself. This is more of a theoretical join and is achieved using any of the previous joins however it grants lots more functionality by allowing records to be compared to other records in the same table.
Union
The union operator is used to combine two result sets, it automatically selects only distinct values so union all can be used instead to provide all the results. For example: SELECT customer FROM coolKids UNION customer FROM smartKids, will give you all unique customer names from both tables. If UNION ALL was used, then all customer names from both tables would be returned.
Group By
Many functions (known as aggregate functions) exist in SQL that allow you to do things such as count the total (count) or find the sum of particular results (sum), however these often require a group by clause to indicate which column these results belong to. For example, “SELECT customer.id, SUM(order.id) … GROUP BY customer.id” would show how many orders each customer has. Basically, these group by statements can be used to categorise data.
Indexes
Indexes are used in databases to speed up queries. They function differently depending on the database provider but predominantly, clustered and non-clustered are used . For lots of database types there can only be one clustered index in a table, and this will be used to define the order of the database. For example, indexing a customer table by (lastname, firstname) will group all instances of the same (last name, firstname)s together. A non-clustered index does not affect the physical layout of the database, rather it provides a link to the database page where the indexed data can be found to help speed up searching in the database. For example, an index on the field city would help speed up queries attempting to find all the customers from a particular city. Of course, indexes on columns that you are not interested in will not help the speed at all.
Indexes can also slow down updates to the table, as the index has to be updated as well as the value.
Tips and Tricks
I have searched the internet high and low for a compilation of useful SQL efficiency suggestions, just for you! And also for me. And perhaps some of the other EPIX staff?
- When UPDATE-ing values, ensure to add where currentValue <> newValue, as this will prevent time wasted redundantly changing values.
- Never use SELECT *, of course.
- Try to avoid using IN with an extremely large number of values.
- Use UNION ALL where possible instead of UNION itself.
- Get as much information as possible from one SQL query instead of using multiple.
- For more efficient queries, avoid DISTINCT as it has a higher query cost, instead SELECT additional fields to individualise the results.
- Use STARTING WITH instead of LIKE where possible.
Different databases have different ways of running the SQL so if you’re aiming for maximum efficiency, it is recommended that you tune your SQL to the suggestions of the database manager itself.
Conclusion
There are so many different ways to write SQL and plenty of keywords that I have not covered yet, nonetheless in terms of knowledge we can use in the future, we are all set for some more advanced SQL writing!
That’s all folks,
Madi.
