Learning SQL can be pretty hard at times. It’s a lot different to other languages.
As a beginner, you may be doing a few things in SQL that work,
but aren’t necessarily the best way of doing something.
In this video, I’ll describe these areas or mistakes, explain why they
aren’t the best approach, and share a better way.
Welcome to the Database Star YouTube channel - the place for developers
looking to improve their database and SQL skills.
Let’s get into it.
The first mistake that beginners often make with SQL is using SELECT * for everything.
A SELECT * query will let you select all columns from a table.
**It’s quick to write, and one of the first parts of writing a Select statement that you learn**.
However, using SELECT * is not recommended for two reasons.
The first is that you get all columns, even those you don’t need.
You will probably not need all columns from a table in your results. Sure, sometimes you
do need every column, but in most cases you don’t. Using Select * will get all columns.
You might be wondering, where’s the harm in that?
You can just ignore the columns you don’t need, right?
Well, you could. But there’s a cost with getting more data than you need. The query
takes longer to run and longer to return results.
There may be memory impacts in your application if more data is included than what is needed.
The second reason is that the order of columns is not guaranteed.
You may use a Select * query to get all columns in a table,
and the columns are shown in a certain order. You could then run the same query on a later date,
and get the columns in a different order.
This is because the order of the columns when using SELECT * is not guaranteed.
They may be in the order that they are created, or the database may return them in a different order.
This may be an issue in your application or your results if you expect them in a certain order.
So, what’s the solution?
The better way to handle this is to specify the columns you need individually.
Add each column to the Select clause, separated by a comma.
That way, you only get the columns you need, and they are in the order that you specify.
Yes, this does take a little longer to type than Select *. But, most IDEs allow you to write this
kind of query easily, with features like auto complete and table aliases and query generators.
And, the typing for a query is not usually the part that takes the most time.
It’s the analysis of the data and working out how to write the query.
Having said that, there is one situation you may want to use Select *. And that’s for small
queries where you want to check what’s in a table. I’ve used it as part of my process
if I want to see data. But I never use it as part of code going into an application.
So, instead of using Select *, select the columns individually.
**The second mistake is writing one query per table**.
If you need to get data from more than one table, you may be tempted to write one query
for each table. For example, one query to get all products, and another query to get all orders.
You could then use your application code, writing
some loops and IF statements to construct a collection of orders and their products.
This will work. But it’s slow and inefficient.
A better way to do this is to use the database for its strengths when working with data.
Use the SQL feature called joins. This allows you to relate the data in one
table to the data in another table, and get the related data in a single result.
If you wanted to get data from both products and orders,
you can use a join on these two tables and select the data you need from both tables.
There are many types of joins. Check out the related video here on joins,
or in the description, to learn more.
So, if you’re considering writing one query per table and then combining them in your application,
consider using joins instead.
The third mistake that beginners to SQL often make is not using functions.
This is another example of something that’s possible in application code,
but it’s often better and easier to do in the database.
Let’s say you needed to get the total of a series of numbers. You could select these
values from the database, loop through them in your application code and calculate a sum.
This would work, but you could use SQL for this.
SQL includes many in-built functions to process data and give you a result.
One example is the SUM function. You can use this to get the total of a series of numbers.
So, if you have a need to get a total of numbers, and you’re doing this in application code,
consider using the SQL SUM function.
Another example is the Count function which gets the number of records.
You could use a function in your application code to get the number of elements in your collection,
for example. And this may be a better approach. However, if you ever want to get a count of
data in groups, or any other function in groups, then you can use these functions.
There are a lot of functions in SQL that you can use to format the data the way you want,
such as dates or strings. You can also concatenate strings and get parts of strings,
and much more, using SQL functions.
So, if there’s something like this that you need, consider using an SQL function.
The fourth mistake I see is using reserved words for table names and column names.
There are some rules or restrictions for naming tables and columns.
They must start with a letter, and they have a maximum length depending on the database vendor.
But, you can generally name them however you like.
However, I would recommend avoiding reserved words for tables.
What are reserved words? These are words that are used by the SQL language,
such as Select and Order. You could name your table “order”, for example,
if you had a table that stored orders in your online store.
However, your IDE may not recognise it as a table and may think it’s part of
your query. The database may not run the query correctly either,
depending on the database vendor and the word used.
You could enclose your table or column inside quotes or brackets,
as I’ve seen many examples of over the years. But this causes a whole range of other problems,
such as readability, difficulty to maintain, and being prone to errors.
A better way is to avoid using reserved words, and to adjust the table name instead. For example,
instead of calling your table “order”, call it something like sales_order or customer_order,
where we have added a bit more information to the table name to indicate what it’s for.
Mistake number 5 I see when beginning SQL
is not using the WHERE clause in a Delete or Update statement. It’s an easy mistake to make.
The Delete and Update statements allow you to make changes to data in a table,
by either deleting records or making changes to records.
They each have a WHERE clause that lets you specify the criteria for the rows to be updated.
For example, to delete all records that are inactive, you specify WHERE status = ‘inactive’.
However, this WHERE clause is optional. You can run a
Delete or Update statement without a Where clause.
This means that the change to data is performed on all records in the table.
If it’s a Delete with no where clause, all rows are deleted.
If it’s an Update with no where clause, all rows are updated.
This is sometimes what you want to do. You may want to delete all records,
or make an update to all records.
But in many cases, it’s not. You may not want to delete or update every record,
but if you forget the Where clause, that’s exactly what will happen.
It’s valid SQL syntax and won’t show an error when you run the query.
So, to avoid this, try to always put a WHERE clause on your Delete and Update statements.
This might make sense, and you may just forget.
A good way to prevent this issue is to start with your WHERE clause first.
Write the WHERE clause to match the data you want to update or delete,
then write the rest of your statement. This will ensure the WHERE clause is always there.
Another way is to add a WHERE clause that says WHERE 1=0. This will return false,
because 1 is not equal to 0, and means that the statement will run but no changes are made.
This is to prevent you from accidentally running the query and causing data issues.
Mistake number 6 that I see is when we try to compare with NULL values.
If we want to compare with a NULL value in our WHERE clause, to include or exclude values of
NULL for example, we may be tempted to write WHERE field = null, or WHERE field != null.
This makes sense. If we want to see rows where a field is null, we write where field = null.
However, in SQL, to compare something to NULL, we use the IS keyword.
We would write, WHERE field IS NULL. If we use equals, we’ll get an error or incorrect results.
So, remember when comparing something to NULL, use IS instead of =. A similar
rule can be applied to not equals, where you use IS NOT instead of !=.
The final mistake in this video that beginners make with SQL is
not saving queries you may want to use again.
It’s easy to open your SQL editor and write a new query.
Some queries are more advanced and take some time to write.
Your SQL editor will allow you to save your SQL queries to a file on your computer.
I would highly recommend doing this for any query that you may want to use again in the future. This
means you can open the file you saved and run the query, instead of writing it again from scratch.
You can also add comments to your file to add more information about the query and what it does.
This can be helpful if you open it again in the future.
If you have a query that you may think you’ll need again, it’s worth saving.
You can save them on your computer for the future.
Once your collection of files starts growing, you’ll start to rely on well-named files
to help you identify what kind of queries the file contains.
You can also use the file search feature in many SQL editors to search for a specific table,
if you want to find all queries that work with a specific table.
I’ve got a video here on using GitHub as a repository for your SQL
files which may be a good way for you to keep your SQL files for the future
and for others, so check it out if this is something you’re interested in.
If you’re interested in improving your database design skills,
take a look at this video on 7 database design mistakes to avoid. It’s similar to this in that
it shows a few common mistakes and shares why they are not advised and a better approach.
If you want to learn more about database design and SQL, visit my website at databasestar.com
If you liked this video, consider subscribing to my channel.
Thanks for watching.