In the lesson about select, we displayed the clients‘ first and last name. Now we want to show more information: the first name and last name of the sales person taking care of each of these clients.
In our example:
- information about sales persons is in a second table called salespersons.
- we want to show information from both tables (clients and salespersons) using one query.
How do you intend to show information from both tables in only one query?
The first name and last name of each client will show on the same line as the first name and last name of the sales person taking care of him.
How will you make sure the right client appears side by side with the right salesperson?
In our example:
- we assign each sales person to a number called an ID. They are like numerical nicknames. The column “ID” of the salespersons table contains these numbers.
- we associate each client with the ID of a salesperson. This is done using a column salesperson_id in the table clients. This column contains the ID of the sales person assigned to each client.
A row in salespersons is matching a row in clients if the following elements are equal:
- salesperson_id from the table clients.
- ID from the table salespersons.
What is the SQL syntax of such a query?
In this query:
- “clients as cl” means that each time “cl.” precedes a column, this column belongs to the table clients. For example “cl.first_name” belongs to the table clients.
- “cl.first_name as clientfirstname” means that in the header (line on top of the results) we will see the text “clientfirstname” on top of the client first names (“cl.first_name”).
- “join salespersons” means that we will show information not only from the table clients but also from the table salespersons. “inner” join is a special type of join. I will explain the different types of joins in a separate lesson.
- “on cl.salesperson_id = sa.id” : this is a condition that a row from the table salesperson must fulfill to show side by side with a row from clients. The condition here is that column “id” from salespersons must be equal to row “saleperson_id” from clients.
Can we use something else than “cl” and “sa” as alias?
You can use almost anything. Just avoid putting special characters in an alias.
Can you join more than two tables?
Yes, you can join more than two tables. Just stay reasonable: joining too many tables can have an impact on the speed at which the database will return the result.
Special technique: replace the second table by a query
You just learnt how to join a table with another table. The syntax is:
select t1.field1,....,t2.field2,.... from table1 t1 inner join table2 t2 where t1.fieldi=t2.fieldsi
In order to execute complicated queries, you can replace the name of the second table by a query.
Example: you want to display, for each row in table sales, the revenue of each transaction divided by the maximum revenue of all transactions in the table. Here is the syntax:
In this query:
- The query in parenthesis is: "select max(revenue) as max_revenue from sales". It gives the maximum revenue of the entire sales table.
- This query is between parenthesis, and just after this parenthesis, you have an alias : s2. As a result, you can access the results column of this query using "s2.".
- As you specified "as max_revenue" just after "select max(revenue)", the result column containing the maximum revenue will be "max_revenue". Therefore, you can use "se.max_revenue" outside of the parenthesis to make your calculation.
More complicated example: you want to display, for each row in table sales, the revenue of each transaction divided by the maximum revenue of all transactions in the same state. In order to do this:
- you will use a where condition in which you will make use of the alias “s2.”
- you have to add the field state in the query in parenthesis in order to use it in the where statement
The syntax is the following:
This technique will help you in many difficult situations.