We analyze databases and the SQL language. (Part 5 - connections 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 one (one-to-one)
  • Many-to-many
• Connections (Joins)
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • Securing Joins
• Homework

Hello everyone, future Seniors and Senioritas of software. As I said in the previous part ( checking homework ), today there will be new material. For those who are especially eager, I have dug up an interesting homework assignment so that those who already know everything and those who don’t know but want to google it 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. For those who forgot, welcome to the beginning of the series .

One-to-many

Let's remember our example with countries and cities. It is clear that a city must have a country. How to link a country to a city? It is necessary to attach to each city a unique identifier (ID) of the country to which it belongs: we have already done this. This is called one of the types of connections - one to many (it would also be good to know the English version - one-to-many). To paraphrase, we can say: several cities can belong to one country. That's how you should remember it: a one-to-many relationship. So far it’s clear, right? If not, then here is the first picture from the Internet: It shows that there are customers and their orders. It makes sense 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 who doesn't want to go bankrupt and wants to be successful has more than one author, don't you agree? In turn, each author may have more than one book - there can be no doubt about that either. And this means, again, the connection of one author to many books, one publisher to many authors . There are a great many more examples that can be given. The difficulty in perception at first may only lie in learning to think abstractly: to look from the outside at the tables and their interaction.

One to one (one-to-one)

This can be said to be a special case of one-to-many communication. A situation in which one record in one table is related to only one record in another table. What examples can there be from life? 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 each wife can still 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 I was writing this, a thought occurred to me: why then divide a one-to-one relationship into two records in different tables, if they already have a one-to-one relationship? I came up with the answer myself. These records may also be linked to other records in other ways. What am I talking about? Another example of one-to-one connections is between the country and the president. Is it possible to write down all the data about the president in the “country” table? Yes, you can, SQL won’t say a word. But if you think that the president is also a person... And he may also have a wife (another one-to-one relationship) and children (another one-to-many relationship) and then it turns out that it will be necessary to connect the country with the president’s wife and children…. 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, unlike a one-to-many relationship.

Many-to-many

Already based on the name, you can guess what we will talk about. Often in life, and we program our lives, 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. There’s just so much connections here... Each publication can have several authors - a one-to-many connection. At the same time, each author may have several publishers (why not, the writer was published in one place, had a fight over money, went to another publishing house, for example). And this is again a one-to-many relationship. Or this: each author can have several books, but each book can also 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 connection types in SQL: we just pass the ID of that one to those records, of which there are many, right? One country gives its ID as a foreign key to many cities. What to do with many-to-many relationships ? This method is not suitable. We need to add another table that would connect the two tables. For example, let's go to MySQL, create a new database manytomany, create two tables, author and book, which will contain 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 used several new features that need to be commented on separately:

• NOT NULL means that the field must 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 exactly 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 old ones? They may not be unique enough, or this approach to creating IDs may no longer be relevant and limited. For this purpose, we made the old ID-name also unique in the table. To check this, you need to add data. Add a 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 a record to the third table: INSERT INTO authors_x_books VALUES (1,1); And everything will be fine until 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 logical question arises: how to get this information? How to combine data from different tables together and get one answer? This is what we will 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 as the eyes of a baby :D Roughly and in general, joins are getting a result from several tables by means of a JOIN (join from English join). And that’s all...) And to join, you need to specify the field by which the tables will be joined. The devil is not as scary as he is painted, right?) Next, we’ll just talk about what types of joins there are and how to use them. There are many types of joins, and we will not consider them all. Only those that we really need. That’s why 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 - pseudonyms. 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. It says that when we have two tables and a field by which it can be joined, all records whose relationships exist in the two tables will be selected. It was difficult to say somehow. Let's look at an example: Let's add one record to our cities database. One entry for cities and one for countries: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); and $ INSERT INTO city (name, population) VALUES("Tbilisi", 1171100); We have added a country that does not have a city in our table, and a city that is not associated with a country in our table. So, INNER JOIN is engaged in issuing all records for those connections that are in two tables. This is what the general syntax looks like when we want to join two tables table1 and table2: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; and then all records that have a relationship in the two tables will be returned. For our case, when we want to receive 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 come first, then the fields of countries. But the two entries that we added above are not there. Because that's exactly how INNER JOIN works.

LEFT JOIN

There are cases, and quite often, when we are not satisfied with the loss of fields of the main table due to the fact that there is no record for it in the adjacent table. This is what a LEFT JOIN is for. If in our previous request we specify LEFT instead of INNER, we will add another city in the response - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; There is a new entry about Tbilisi and everything that relates to the country is in null . 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, but all countries will be taken: $ 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…))

Securing Joins

Now I want to show you a typical picture that juniors cram before an interview to convince them that 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. Let's look:

• INNER JOIN is only the intersection of sets, that is, those records that have connections to two tables - A and B;
• LEFT JOIN is all records from table A, including all records from table B that have an intersection (connection) 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 rest assured that they are ready to start working on the SQL side! The tasks are not chewed up and were written for middle students, so it won’t be easy and boring for you :) I will give you a week to do the tasks yourself, and then I will publish a separate article with a detailed analysis of the solution to the tasks that I gave you.

The actual task:

1. Write an SQL script to create the 'Student' table with the following fields: id (primary key), name, last_name, e_mail (unique).
2. Write an SQL script to create the 'Book' table with the following fields: id, title (id + title = primary key). Link 'Student' and 'Book' with a 'Student' one-to-many 'Book' relationship.
3. Write an SQL script to create the 'Teacher' table with the following fields: id (primary key), name, last_name, e_mail (unique), subject.
4. Link 'Student' and 'Teacher' with a 'Student' many-to-many Teacher' relationship.
5. Select 'Student' who have 'oro' in their last name, for example 'Sid oro v', 'V oro novsky'.
6. Select from the 'Student' table all last names ('last_name') and the number of their repetitions. Consider 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 the top 3 most repeated names from 'Student'. Sort by quantity in descending order. It should look like this:

   name	quantity
   Alexander	27
   Sergey	10
   Peter	7

8. Select 'Students' who have the largest number of 'Book' and associated 'Teacher'. Sort by quantity 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 largest number of 'Book' from all his 'Student'. Sort by quantity in descending order. It should look like this:

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

10. Select 'Teacher' whose number of 'Book' for all of his 'Student' 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 in (numeric value from 1 to 6).
13. This item is not required, but will be a plus. Write a function that will go through all 'Books' and output all 'titles' separated by commas.

Conclusion

The series about the database has dragged on a bit. Agree. However, we have come a long way and as a result we emerge with knowledge of the matter! Thank you all for reading, I remind you that everyone who wants to move on and follow the project needs to create an account on GitHub and subscribe to my account :) More to come - let's talk about Maven and Docker. Thanks everyone for reading. I repeat once again: the one who walks will master the road ;)

A list of all materials in the series is at the beginning of this article.