SQL Tutorial part :6 WHERE IN

SQL Tutorial part :6  WHERE IN
Image

In the previous tutorial, we will bring the data using the WHERE .. IN clause in the “users” table. Suppose I need users between the ages of 33, 25, and 26, then type the following query and run it.

Image

Output

Image

Or suppose I just want to see the payroll and email of the user with these two usernames “rejoan” and “kollol”

Image

Running it will only show these two rows.

Is it too easy to get bored? These have to be learned. When working on large applications you will see how often they are needed and the data inside IN that is statically queried here will have to be brought dynamically with PHP.

SQL Join :

Some joins are used in the sequel to extract data from two related tables. Most of the time 3 joins are used. INNER JOIN, II. LEFT JOIN, 3. RIGHT JOIN

 

This tutorial discusses Inner Join

 

INNER JOIN

 

First let’s create two tables that have a relation between them

 

Below is the code for creating another “profiles” table with one-to-one relation to the “users” table that we created earlier in this tutorial.

Image

This query invoice will create a table with the following data.

Image

The INNER JOIN can be used to retrieve data (rows) from which the relation exists.

 

Run the following query to retrieve data from the two tables above

Image

Its output will be 3 rows. Because look at the “user_id” column in the “profiles” table which is the foreign key of this table, there is one row for users number 1, 2 and 3. If there were no rows here, no output would come, if there was a row, an output would come.

 

This query can be done in more ways

Image

The USING keyword can only be used when the “user_id” column has the same name on both tables. The two tables are connected with this “user_id” column, let’s name it Bridge Column :).

Great SQL Left Join :

Previously created “users” and “profiles” tables will work, so running this query in that database will work.

 

With the LEFT JOIN clause, all the data or rows in the “users” table (this is called the left table) will move. Suppose there are 10 rows in the left table, then the output will come in 10 rows, if in the “profiles” table There are rows that match 5 of those 10 (related) but still show the output of 10 rows.

Image

Output

Image

See NULL is coming to the rows of the left table for which there is no related row in the right table. There are only 3 rows coming from the right table because there are only 3 related rows.

Great SQL Right Join :

Previously created “users” and “profiles” tables will work, so running this query in that database will work.

 

With the RIGHT JOIN clause all the data / rows in the “profiles” table will come and only those rows in the “users” table will come for which there are any (related) rows in the right table.

Image

Output

Image

See all of the right tables are coming and only 3 rows of the left table are coming, because these 3 rows are related to the right table. No other rows are coming because there is no related row for them in the right table.

 

 

** I have deleted some rows from the two tables so there may be some discrepancies in the output. It does not matter because if you understand the code then the output will not seem to be a problem. I did everything but the output did not match your picture. “

 

 

SQUEL GROUP BY AND ORDER BY :

Now we will discuss two more clauses with GROUP BY and ORDER BY. HAVING clauses. These are used to filter all the data.

GROUP BY

Suppose you have to create a database for a training institute, if you do 5 courses there, then there will be many students for each course. Now there is a table where the data of the students is like below.

Image

The code for creating the table

Image

From here you can filter the data with GROUP BY. Here are a few rows so at a glance you may have already grouped the data in your mind. But when there will be thousands of millions of data, the need will be strong.

Image

Run this query, there will be 3 rows because there are 3 groups, see “course_name”. In this way, group-based data is filtered by the column name.

Suppose I need the average age of the people in each group, then it can be easily seen by writing a query like below

Image

Output

Image

You can filter out more here with the keyword HAVING and see specific groups, for example, if I want to average the age, if the average age is above 30, then I will look at that group.

Image

View the output. A row will appear. Now the question may be why HAVING is used? This would be done with WHERE. The answer is that WHERE will not work here because it only filters rows and with HAVING one group is being filtered here. This is an important difference to keep in mind.

Another point to keep in mind is that the column you BY GROUP BY will be in the SELECT statement. There is no problem using another column in the aggregate function tutorial as I did but you have to select the column you will GROUP BY with.

ORDER BY

This is the work of sorting or sorting data. When you query from many rows / data with SELECT, you want to see those rows from small to large or in reverse or in literal order (A to Z / Z to A). This ORDER BY. ORDER BY can be done with any column. By default data will come in this order from small to large. If you want the opposite, you just have to give DESC after the name of the column.

Image

Look at the output. Everyone has “age” in the first row which has 56 wires. If you want, give ASC instead of small to big DESC. And after ORDER BY, see I have sorted with “age” column. Then the data will come in alphabetical order. In this way data can be sorted with any column.

If you need to use with GROUP BY, then ORDER BY should not be given before but after GROUP BY.

Image

The data will be sorted according to “age”. If I want to sort according to “course_name”

Image

Leave a Reply

Your email address will not be published. Required fields are marked *