Understanding databases and SQL. (Part 5 - links and joins) - "Java project from A to Z"

An article from a series about creating a Java project (links to other materials are at the end). Its goal is to analyze key technologies, the result is to write a telegram bot.

• Types of relationships in the database
  • One to many (one-to-many)
  • One to one (one to one)
  • Many to many (many-to-many)
• Connections (Joins)
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • Fixing Joins
• Homework

Hello, future Seniors and Seniors of software. As I said in the previous part ( checking homework ), there will be new material today. For those who are especially thirsty, I have dug up an interesting homework assignment so that those who already know everything and those who do not know, but want to google, can practice and test their skills. Today we will talk about types of connections and joins.

Types of relationships in the database

To understand what relationships are, you need to remember what a foreign key is. Who forgot - wellcome to the beginning of the series .

One to many (one-to-many)

Recall our example with countries and cities. It is clear that the city must have a country. And how to tie a country to a city? It is necessary to attach a unique identifier (ID) of the country to which it belongs to each city: we have already done this. This is called one of the types of relationships - one to many (it would be nice to know the English version - one-to-many). To paraphrase, we can say: several cities can belong to one country. This is how you should remember it: a one-to-many relationship. So far so clear, right? If not, then here is the first picture from the Internet: It shows that there are customers and their orders. After all, it is reasonable that one customer can have more than one order. There is one-to-many :) Or another example:There are three tables: publisher, author and book. Every publisher that doesn't want to go bankrupt and wants to be successful has more than one author, right? In turn, each author can have more than one book - there can be no doubt about that either. And this means, again, the relationship of one author to many books, one publisher to many authors . Many more examples could be cited. The difficulty in perception at first can only be to learn how to think abstractly: to look from the outside at the tables and their interaction.

One to one (one to one)

This is, one might say, a special case of a one-to-many relationship. A situation in which one record in one table is related to only one record in another table. What are real life examples? If we exclude polygamy, then we can say that there is a one-to-one relationship between husband and wife. Although even if we say that polygamy is allowed, then all the same, each wife can have only one husband. The same can be said about parents. Each person can have only one biological father and only one biological mother. Explicit one-to-one relationship. While writing this, the thought came to mind: why then split the one-to-one relationship into two records in different tables, if they already have an unambiguous relationship? I came up with the answer myself. These entries can still be linked to other entries in other links. What am I talking about? Another example of a one-to-one relationship is a country and a president. Is it possible to write down all the data about the president in the “country” table? Yes, you can, SQL will not say a word. But if you think that the president is also a person ... And he can also have a wife (another one-to-one relationship) and children (another one-to-many relationship), and then it turns out that it will already be necessary to connect the country with the wife and children of the president .... Sounds crazy, right? :D There can be many other examples for this connection. Moreover, in such a situation, you can add a foreign key to both tables, in contrast to a one-to-many relationship. And he can also have a wife (another one-to-one relationship) and children (another one-to-many relationship), and then it turns out that this country will already need to be connected with the wife and children of the president .... Sounds crazy, right? :D There can be many other examples for this connection. Moreover, in such a situation, you can add a foreign key to both tables, in contrast to a one-to-many relationship. And he can also have a wife (another one-to-one relationship) and children (another one-to-many relationship), and then it turns out that this country will already need to be connected with the wife and children of the president .... Sounds crazy, right? :D There can be many other examples for this connection. Moreover, in such a situation, you can add a foreign key to both tables, in contrast to a one-to-many relationship.

Many to many (many-to-many)

Already from the name, you can guess what will be discussed. Often in life, and we program our life, there are situations when the above types of connections are not enough to describe the things we need. We have already talked about publishers, books and authors. Here, just like that, there are links ... Each publication can have several authors - a one-to-many relationship. At the same time, each author can have several publishers (why not, the writer was published in one place, had a fight over money, went to another publishing house, for example). Again, this is a one-to-many relationship. Or so: each author can have several books, but each book can have several authors. Again, a one-to-many relationship between author and book, book and author. From this example, we can draw a more formalized conclusion: It was clear how to set the previous types of relationships in SQL: we just pass the ID-shnik of the fact that there is one in those records, of which there are many, right? One country gives its ID-shnik as a foreign key to many cities. What about many-to-many relationships ? This method is not suitable. We need to add another table that links the two tables. For example, let's go to MySQL, create a new database manytomany, create two tables, author and book, in which there will be only names and their IDs: CREATE DATABASE manytomany; USE manytomany; CREATE TABLE author( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY (id) );Now let's create a third table that will have two foreign keys from our author and book tables, and this link will be unique. That is, it will not be possible to add a record with the same keys twice: CREATE TABLE authors_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES author(id ), UNIQUE (book_id, author_id) ); Here we have used several new features that need to be commented separately:

• NOT NULL means the field should always be filled in, and if we don't, SQL will tell us so;
• UNIQUE says that a field or a bunch of fields must be unique in the table. It often happens that in addition to the unique identifier, one more field must be unique for each record. And UNIQUE is responsible for just this matter.

From my practice: when moving from an old system to a new one, we, as developers, must store the IDs of the old system to work with it and create our own. Why create your own, and not use the old ones? They may not be unique enough, or such an approach to creating IDs is no longer relevant and limited. To do this, we made the old ID-shnik also unique in the table. To check this, you need to add data. Add book and author: NSERT INTO book (name) VALUES ("book1"); INSERT INTO author(name) VALUES("author1"); We already know from previous articles that they will have IDs 1 and 1. Therefore, we can immediately add an entry to the third table: INSERT INTO authors_x_books VALUES (1,1); And everything will be fine until the moment when we want to repeat the last command again: that is, write down the same IDs again: The result will be natural - an error. There will be a duplicate. The entry will not be recorded. This is how a many-to-many connection will be created ... All this is very cool and interesting, but a natural question arises: how to get this information? How to combine data from different tables together and get one answer? That's what we'll talk about in the next part))

Connections (Joins)

In the previous part, I prepared you to immediately understand what joins are and where to use them. Because I am deeply convinced that as soon as understanding comes, everything will immediately become very simple, and all articles about joins will be clear, like the eyes of a baby: D Roughly and in general, joins are getting results from several tables by JOIN (join from English join). And that's all ...) And in order to connect, you need to specify the field by which the tables will be connected. The devil is not as scary as he is painted, right?) Next, let's just talk about what kind of joins are and how to use them. There are many types of joins, and we will not consider all of them. Only the ones we really need. Therefore, we are not interested in such exotic joins as Cross and Natural. I completely forgot, we need to remember one more nuance: tables and fields can have aliases- aliases. They are conveniently used for joins. For example, you can do this: SELECT * FROM table1; if the query will often use table1, then you can give it an alias: SELECT* FROM table1 as t1; or even easier to write: SELECT * FROM table1 t1; and then later in the query it will be possible to use t1 as an alias for this table.

INNER JOIN

The most common and simple join. He says that when we have two tables and a field by which it can be connected, all records will be selected, the links of which exist in two tables. It's hard to say somehow. Let's look at an example: Let's add one record to our cities database. One entry for cities and one entry for countries: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); and $ INSERT INTO city (name, population) VALUES("Tbilisi", 1171100); We've added a country that doesn't have a city in our table, and a city that doesn't have a country in our table. So, INNER JOIN is engaged in the fact that it gives out all the records for those connections that are in the two tables. Here is what the general syntax looks like when we want to join two tables table1 and table2:INNER JOIN table2 ON t1.id = t2.t1_id; and then all the records that have a relationship in the two tables will be returned. For our case, when we want to get information for countries along with cities, it will turn out like this: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Here, although the names are the same, you can clearly see that the fields of cities go first, then the fields of countries. And those two entries that we added above are not there. Because that's how INNER JOIN works.

LEFT JOIN

There are cases, and quite often, when we are not satisfied with the loss of the fields of the main table due to the fact that there is no record for it in the adjacent table. For this case, LEFT JOIN is needed. If we indicate in our previous request instead of INNER - LEFT, we will add one more city in the answer - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; There is a new entry about Tbilisi and everything related to the country is null there . This is often how it is used.

RIGHT JOIN

Here there will be a difference from LEFT JOIN in that all fields will be selected not on the left, but on the right in the connection. That is, not cities will be taken, but all countries: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Now it is clear that in this case there will be no Tbilisi, but we will have Uzbekistan. Something like that…))

Fixing Joins

Now I want to show you a typical picture that juniors cram before an interview to make sure they understand the essence of joins: Here everything is shown in the form of sets, each circle is a table. And those places where it is painted over are those parts that will be shown in SELECT. We look:

• INNER JOIN is only the intersection of sets, that is, those records that have links to two tables - A and B;
• LEFT JOIN - these are all records from table A, including all records from table B that have an intersection (link) with A;
• RIGHT JOIN is exactly the opposite of LEFT JOIN - all records in table B and records from A that have a relationship.

After all this, this picture should be clear))

Homework

This time the tasks will be very interesting and all those who successfully solve them can be sure that they are ready to start working from the SQL side! The tasks are not chewed and were written for the middles, so you won’t be easy and bored :) I will give you a week to do the tasks yourself, and then I will release a separate article with a detailed analysis of the solution of those tasks that I gave you.

The actual task:

1. Write SQL script to create table 'Student' with fields: id (primary key), name, last_name, e_mail (unique).
2. Write SQL script to create table 'Book' with fields: id, title (id + title = primary key). Associate 'Student' and 'Book' with a 'Student' one-to-many 'Book' relationship.
3. Write SQL script to create table 'Teacher' with fields: id (primary key), name, last_name, e_mail (unique), subject.
4. Associate 'Student' and 'Teacher' with 'Student' many-to-many Teacher' relationship.
5. Select 'Student' who have 'oro' in their last name, for example 'Sid oro v', 'V oronovsky '.
6. Select from the table 'Student' all last names ('last_name') and the number of their repetitions. Assume that there are namesakes in the database. Sort by quantity in descending order. It should look like this:

   last_name	quantity
   Petrov	15
   Ivanov	12
   Sidorov	3

7. Select from 'Student' the top 3 most repeated 'name' names. Sort by quantity in descending order. It should look like this:

   name	quantity
   Alexander	27
   Sergey	10
   Peter	7

8. Select the 'Student' who has the largest number of 'Book' and associated 'Teacher'. Sort by number in descending order. It should look like this:

   Teacher's last_name	Student's last_name	Book's quantity
   Petrov	Sidorov	7
   Ivanov	Smith	5
   Petrov	Kankava	2>

9. Select the 'Teacher' who has the highest number of 'Books' from all of his 'Student's. Sort by quantity in descending order. It should look like this:

   Teacher's last_name	Book's quantity
   Petrov	9
   Ivanov	5

10. Select a 'Teacher' whose number of 'Books' for all his 'Student's is between 7 and 11. Sort by quantity in descending order. It should look like this:

    Teacher's last_name	Book's quantity
    Petrov	eleven
    Sidorov	9
    Ivanov	7

11. Print all 'last_name' and 'name' of all 'Teacher' and 'Student' with field 'type' (student or teacher). Sort alphabetically by 'last_name'. It should look like this:

    last_name	type
    Ivanov	student
    Kankava	teacher
    Smith	student
    Sidorov	teacher
    Petrov	teacher

12. Add a 'rate' column to the existing 'Student' table, which will store the course the student is currently on (a numeric value between 1 and 6).
13. This item is not mandatory, but it will be a plus. Write a function that will iterate through all 'Book's and output all 'title' separated by commas.

Conclusion

The series about the database was somewhat delayed. Agree. However, we have come a long way, and as a result, we exit with skill! Thank you all for reading, I remind you that everyone who wants to go further and follow the project needs to create an account on GitHub and follow my account :) Further more - let's talk about maven and docker. Thank you all for reading. I repeat once again: the road will be mastered by the walking one;)

List of all materials in the series at the beginning of this article.