Shulou(Shulou.com)05/31 Report--

What this article shares to you is about how to connect the JOIN table in SQL, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

CROSS JOIN (Cross Connect)

The most basic JOIN operation is the true Cartesian product. It simply combines each row in one table and each row in another table. Wikipedia gives an example of the Cartesian product with a pair of cards, cross-joining the ranks table and the suits table:

In real-world scenarios, CROSS JOIN is very useful when executing reports, for example, you can generate a set of dates (such as days in a month) and cross-connect with all departments in the database to create a complete day / department table. Use the PostgreSQL syntax:

Imagine that we have the following data:

The result will now be as follows:

+-+-+ | day | department | +-+-+ | Jan 01 | Dept 1 | | Jan 01 | Dept 2 | | Jan 01 | Dept 3 | | Jan 02 | Dept 1 | | Jan 02 | Dept 2 | | Jan 02 | Dept 3 | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | Jan 31 | Dept 3 | +-- | -+

Now, in each day / department mix, you can calculate the department's daily income, or other.

Characteristics

CROSS JOIN is the product of Cartesian, that is, the product of multiplication. The mathematical symbol uses a multiplication sign to indicate this operation: a × B, or in this example: days × departments.

As with ordinary arithmetic multiplication, if one of the two tables is empty (size zero), the result will also be empty (size zero). This makes perfect sense. If we combine the previous 31 days with 0 departments, we will get 0 days / department combination. Similarly, if we combine the empty date range with any number of departments, we will also get a 0-day / department combination.

To put it another way:

Size (result) = size (days) * size (departments)

Alternative grammar

In the past, before ANSI JOIN syntax was introduced to SQL, people would write CROSS JOIN by writing a comma-separated list of tables in the FROM clause. The above query is equivalent to:

SELECT * FROM generate_series ('2017-01-01'::TIMESTAMP,' 2017-01-01'::TIMESTAMP + INTERVAL'1 month-1 day', INTERVAL'1 day') AS days (day), departments

In general, I strongly recommend using the CROSS JOIN keyword instead of a comma-separated list of tables, because if you deliberately want to execute CROSS JOIN, nothing conveys this intention better than using the actual keyword (for the next developer). What's more, there are so many places in the comma-separated list of tables that can go wrong. You certainly don't want to see such a thing!

INNER JOIN (Theta-JOIN)

Building on previous CROSS JOIN operations, INNER JOIN (or just a simple JOIN, sometimes called "THETA" JOIN) allows you to filter the results of Cartesian products through certain predicates. Most of the time, we put this predicate in the ON clause, and it might look like this:

SELECT *-- Same as before FROM generate_series ('2017-01-01'::TIMESTAMP,' 2017-01-01'::TIMESTAMP + INTERVAL'1 month-1 day', INTERVAL'1 day') AS days (day)-- Now, exclude all days/departments combinations for-- days before the department was created JOIN departments AS d ON day > = d.created_at

In most databases, the INNER keyword is optional, so I omitted it in this article.

Notice how the INNER JOIN operation allows arbitrary predicates to be placed in the ON clause, which is also useful when executing reports. As in the previous CROSS JOIN example, we combine all dates with all departments, but we keep only the day / department combinations that already exist for those departments, that is, the department was created before the day.

Again, use this data:

+-+ | day | | department | created_at | +-+ | Jan 01 | | Dept 1 | Jan 10 | | Jan 02 | | Dept 2 | Jan 11 | | Dept 3 | Jan 12 | | Jan 30 | +-+ | Jan 31 | +-+ |

The result will now be as follows:

+-+-+ | day | department | +-+-+ | Jan 10 | Dept 1 | | Jan 11 | Dept 1 | | Jan 11 | Dept 2 | | Jan 12 | Dept 1 | | Jan 12 | Dept 2 | | Jan 12 | Dept 3 | | Jan 13 | Dept 1 | | Jan 13 | Dept 2 | | Jan 13 | Dept 3 | |... | | Jan 31 | Dept 1 | | Jan 31 | Dept 2 | | Jan 31 | Dept 3 | +-+-+ |

Therefore, we don't have any results until January 10, because these rows are filtered out.

Characteristics

The INNER JOIN operation is a filtered CROSS JOIN operation. This means that if one of the two tables is empty, the result is also guaranteed to be empty. But unlike CROSS JOIN, because of the existence of predicates, we can always get fewer results than CROSS JOIN provides.

To put it another way:

Size (result) generateSeries (department.createdAt, LocalDate.parse ("2017-01-31")) .map (day-> tuple (department, day)

What's going on here?

The DEPARTMENTS table is just a Java department stream

We use a function that generates tuples for each department to map the department stream

These tuples include the department itself and a day generated from a series of dates starting from the department CreatedAt date

Same story! SQL CROSS APPLY / CROSS JOIN LATERAL is the same as Java's Stream.flatMap (). In fact, SQL and stream are not too different. For more information, please read this blog article.

Note: just as we can write LEFT OUTER JOIN LATERAL, we can also write OUTER APPLY to keep the left side of the JOIN expression.

MULTISET

Few databases implement this (in fact, only Oracle), but if you think about it, it's a really great JOIN type. A nested collection is created. If all databases implement it, then we don't need ORM!

Take a hypothetical example (using SQL standard syntax instead of Oracle), like this:

SELECT A. fa.actor_id, MULTISET (SELECT f. * FROM film AS f JOIN film_actor AS fa USING (film_id) WHERE a.actor_id = fa.actor_id) AS films FROM actor

The MULTISET operator uses related subquery parameters and aggregates all of its generated rows in a nested collection. This is similar to the way LEFT OUTER JOIN (we got all the actors, and if they were in the movie, we got all their movies), but instead of copying all the actors in the result set, we collected them into nested collections.

As we did in ORM, when getting things into this structure:

@ Entityclass Actor {@ ManyToMany List films;} @ Entityclass Film {}

Ignoring the incompleteness of the JPA annotations used, I just want to show the strength of nested collections. Unlike in ORM, the SQL MULTISET operator allows arbitrary results of related subqueries to be collected into nested sets-- not just actual entities. This is millions of times better than ORM.

Alternate syntax: Oracle

As I said, Oracle actually supports MULTISET, but you can't create ad-hoc nested collections. For some reason, Oracle chose to implement nominal typing for these nested collections, rather than the usual SQL-style structure typing. So you have to declare your type in advance:

It's a little longer, but it's still a success! That's awesome!

Alternative syntax: PostgreSQL

The great PostgreSQL lacks an excellent SQL standard feature, but there is a solution: arrays! This time, we can use structural types. Wow! So the following query returns a nested array of rows in PostgreSQL:

SELECT an AS actor, array_agg (f) AS filmsFROM actor AS aJOIN film_actor AS fa USING (actor_id) JOIN film AS f USING (film_id) GROUP BY a

The result is everyone's ORDBMS dream! Nested records and collections are ubiquitous (only two columns):

Actor films-- (1Perelope DINOSAUR) {(23rd CONFESSIONS),.} (2Nike HOLES) {(31pl APACHE DIVINE),...} (3Zhi EDEDCHASE) {(17parol all TRIP), (40MY FLINTSTONES) ...} above is how to join the JOIN table in SQL. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.