How to create MySQL combinations with Inner Join?

Combinations are essential when we talk about MySQL , taking into account that thanks to them we will have the possibility to do all kinds of queries on the tables of a previously generated database.

The best thing about them is that they allow us to consider different criteria to get a particular result, a key problem thinking that many of these queries are also performed on the construction of an SQL statement, called a predicate .

If you don’t know what we are talking about or how to take advantage of these combinations, don’t worry, as in the following lines we will show you all the details you should consider in these types of cases.

Use MySQL combinations

These combinations MySQL, used for example in the case of programming universities, become useful once decompressed the downloaded file, when it will appear two so-called Sakila-schema.sql and Sakila-data.sql .

They contain the database structure and sample data. On this occasion we will rely, for example, on a database that aims to provide a test environment for software development or SQL queries .

The first thing to consider in this regard is that Sakila’s structure is by no means simple, since it has around 1000 records as well as a huge amount of previous relationships, with examples of views, stored procedures, etc.

Suppose, for example, that this conformation is like a chain of stores movie rental that both branches and a huge number of vendors for each of them.

Anyone who wants to rent a movie , therefore, will first have to confirm that it is available or available, then the seller will also verify that it is part of the database to assign the DVD to us. And there are also payslips, outstanding balances, etc.

On this occasion, for example, if we want to know if the customer exists , the query will look like:

select customer.customer_id, customer.store_id, CONCAT (customer.first_name, “, customer.last_name) as customer

by the customer

where customer.first_name as’ Juan%

Now, from this we want to show you what queries we have available by combining all the available data.

SQL Inner Join Clauses

Well, as we said, a JOIN SQL clause is normally used to combine different rows from two or more tables, taking advantage of a common field that can exist between them. What the system does is return only those who meet the union condition .

Let’s see an example that follows the movie rental shop tonic we mentioned earlier, so that there is no doubt about the ways we can take advantage of this tool.

If by any chance we want to know which customers have rented movies , the first thing we need to do is use the basic tables that are the customer and the rental. The first thing is to know who is inside the second like this:

select rental.rental_id, customer.first_name, customer.last_name, rental.rental_date, rental.return_date

by the
customer INNER JOIN for hire

ON rental.customer_id = customer.customer_id

On the screen we should observe, from this, a list of customers who have rented films in different stores.

Another option is to consult a Where , although the procedure here may take a little longer:

select rental.rental_id, customer.first_name, customer.last_name, rental.rental_date, rental.return_date
from rental, customer

where rental.customer_id = customer.customer_id

As you can see, after installing MySQL on your computer you will have a great deal of options available to take advantage of this platform, and this is just one of the many that we have analyzed in Miracomohacerlo .

Leave a Comment