Selecting data from multiple tables

dependent table

Now let's complicate our queries a bit. Let's add a new task table to our database with tasks for our employees. And let's see what entries it contains:

SELECT * FROM task

The result of such a request:

This table has only 4 columns:

• id — unique task number (and rows in the table);
• employee_id — ID of the employee from the employee table to whom the task is assigned;
• name — name and description of the task;
• deadline - the time by which the task must be completed.

Pay attention to a few nuances. Task N6 doesn't have an employee_id, we don't have a cleaner. The task is there, but the performer is not. It happens.

Also, tasks 6-9 do not have a set deadline. This happens when a task must be done regularly and continuously. For example, the office needs to be cleaned every day, but you also need to enjoy life every day :)

If one table uses IDs from another table, then such a table is called dependent .

Query against multiple tables

Here we see in the task table that there are two tasks “Enjoying life”. How do we know who these lucky ones are?

To do this, in SQL, you can execute a query on two tables at once. In general, in SQL, you can query any number of tables at the same time. The general format for such a request is:

SELECT columns
FROM Table 1, table 2, tableN

Important! If you write a query to several tables at the same time, then as a result you will get the so-called Cartesian product of table rows. Each row from the first table will be glued to each row from the second table, and so on.

That is, if you have 5 rows in the first table and 10 rows in the second, then you will have 50 rows in total. In Java, this query would look something like this:

for (String row1 : table1)
{
	for (String row2 : table2)
   {
  	System.out.println(row1 + row2);
   }
}

Let's write our query to two tables at once and see what happens:

SELECT * FROM employee, task

And the result of this query:

We have 48 result lines in total, but here I have given only 11. Otherwise, there simply won’t be enough space.

Pay attention to three things:

• Columns with the same name: id . This is the id from the employee table and the id from the task table .
• The rows of each table are repeated. In the left column, ID 6 is followed by ID = 1 again.
• We have nonsense rows where, for example, id (from the employee table) is 6 and in the same row employee_id is 1.

Removing meaningless lines

There are too many rows in our resulting table, which is the Cartesian product of all the rows of the two tables employee and task .

Logically, if the row employee_id is 3, then it should only stick to the row from the employee table where id is 3. Let's try to fix this misunderstanding with WHERE.

Let's write a query like this:

SELECT * FROM employee, task 
WHERE emploee.id = task.emploee_id 

And the result of this query:

The good news is that the meaningless lines have disappeared: the id from the first column is always equal to employee_id.

The bad news is that tasks that are not assigned to anyone, such as cleaning the office, are gone. Their employee_id was NULL, so they were discarded after the WHERE was done.

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0301

task0301

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0302

task0302

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0303

task0303

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0304

task0304

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0305

task0305

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0306

task0306

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0307

task0307

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0308

task0308

Open

1

Task

Module 4. Working with databases, level 3, lesson 0

Locked

task0309

task0309

Open