MySQL Concepts: How to Retrieve Data Using SELECT and WHERE Clauses in MySQL?

Hello everyone, welcome back to CybercityHelp. So in our today’s article, we will be discussing about how we can use both the SELECT and WHERE clauses to retrieve data in MySQL. So, let’s get started.

However, if you’re completely a new user who doesn’t know much about MySQL basics, we recommend you first learn those basics of MySQL. You can learn them from this link: MySQL Basics

What is SELECT Clause?

The SELECT clause is basically the first step behind retrieving any data from the database. Because It informs the MySQL like how exactly which columns or data the user wants to view. This clause is very basic but extremely important because without this clause, we cannot fetch any data from the tables.

The SELECT clause is only responsible for retrieving the data from the database. Here along with SELECT clause, the asterisk (*) sign is also very important because it controls everything. So, if you want to retrieve each and every column from the database table, you need to write queries along with (*) sign.

How to Retrieve Data using SELECT Clause?

Now let’s also understand, how we can retrieve the data using SELECT clause. So it’s quite simple, you just have to define from which table you need to call this data. So, you can use FROM clause and then the Table Name.

Here FROM clause is nothing, just it is used to define the location of the data. For example, let’s say, we want to retrieve a table of data from MySQL, so for that, first we need a database to retrieve the data.

Let’s say, we have a database called classicmodels. So if we open this database, we get lots of tables that are there in the database. So if we click on query window, it will open the query for me, and the query will look like:

SELECT * FROM classicmodels.customers;

For example:

See here the SELECT clause and (*) sign and FROM clause all are used together to retrieve the data from desired locations. Here in the same way, you have to write your query properly to retrieve the data. In our case, we are using classicmodels as our database and customers is our one of the table of database. And we are retrieving the data of customers table of our classicmodels database.

Well, this query will be there automatically in the query window only if your database has already lots of tables and data is there in the table. In case if you want to retrieve data from any other table, you can manually edit the query as per your need to retrieve the data. For example, you can edit the query in this way:

SELECT * FROM products;

For example:

Here we have just slightly changed the table from customers to products and the database is still same. So this is how you can edit the query and retrieve the data accordingly.

But note that, sometimes, even if you write the correct query, you might face issues in retrieving the data. One common problem is that MySQL might say a table does not exist.

This usually happens because the database is not properly highlighted. Make sure whenever you are calling a table, just double-click on that database to highlight it. Highlight it like this so that it looks like bold characters. Now, if you run the query, it should work properly without any issues or interruptions.

How to Retrieve Data from Specific Columns?

Now what happens sometimes, we don’t need to retrieve the data from the tables or database, sometimes we need to retrieve the data from columns only, so how we can do that? So In that case, you can simply choose only specific columns that you want to retrieve.

For example, if we want to retrieve only some columns like product name, product line, and quantity in stock, so we just have to slightly edit our query, like this:

SELECT productName, productLine, quantityInStock
FROM products;

For example:

So we just have to follow this format and don’t forget to close your query with a semicolon (;). Because If you don’t put a semicolon, it will show you an error. Always make sure to close your queries with a semicolon. So this is how, you can slightly edit your query and retrieve the data accordingly using different clauses of MySQL.

What is WHERE Clause?

Now we have WHERE clause as well to retrieve the data. But what this is exactly? So WHERE clause is used when we want to retrieve the data based on specific conditions. It allows us to filter the data and get only the results that we want to retrieve.

Let’s understand this clause with an example. We are going to take the same database and same table. We will just explain, how exactly this WHERE clause can be used, so let’s edit the query slightly with WHERE clause.

How to Retrieve the Data using WHERE Clause?

Let’s say, we want to retrieve data only from a specific country, for example, only from USA, so we will have to slightly modify the query from the default query to updated query.

So you may get the default query like this in the query window:

SELECT * FROM classicmodels.customers;

So you just have to write the query in the same way to use WHERE clause, like this:

SELECT * FROM customers
WHERE country = "USA";

For example:

However, always make sure when you’re writing any country name like USA, put it inside double quotation marks because textual values use quotes, and these are supposed to be in under double quotation.

So this is how data will be retrieved specifically using WHERE clause, if you want, you can retrieve data for cities too, so just slightly you have to edit the query again like this:

SELECT * FROM customers
WHERE city = "NYC";

For example:

Here we have all the data of customers who live in New York City. You can also combine multiple conditions using AND or OR if needed:

SELECT * FROM customers
WHERE country = "USA" AND city = "NYC";

Use of Greater Than (<) and Smaller Than (>) in WHERE Clause Conditions

Since WHERE clause is mostly used when we have some conditions. And conditions are nothing if we are not using greater than (>) or smaller than (<) because these condition operators are responsible for filtering numeric values using greater than, smaller than, and (=) for exact matches.

SELECT * FROM customers
WHERE creditLimit > 100000;

For example:

This will retrieve only the products whose credit limit is greater than 1 lakh. Similarly, you can use smaller than conditions to filter data as per your needs.

How to Retrieve Data using both SELECT and WHERE Clause?

Using WHERE clause and SELECT clause, we can retrieve the data more accurately if we are using the combination of both the clauses. For example we can even retrieve the data from any specific column, just you have to write the query like this:

SELECT customerName, country, customerNumber
FROM customers WHERE creditLimit > 100000;

For example:

See here in this way, we can exactly retrieve the data more accurately from any column we want. Just like we are retrieving the data of customer names from the customer table of classic models database with a condition for the customers like who has a credit limit lower than 1 lakh will be retrieved. So this is the example of WHERE clause with all other clauses.

Alright, so we hope this much of explanation about all the clauses like SELECT and WHERE etc might be enough to understand it’s usages. We have tried our best to explain these clauses in the simplest language possible. Just read carefully and apply the examples shown in this article.

We hope you learned everything well. If not, let us know in the comment section. We will meet again in the next article with more concepts. So yeah, that’s all for today’s article, thank you so much for reading till the end!!

“So keep learning, keep growing!”