Shulou(Shulou.com)06/02 Report--

This article mainly introduces the SQL left connection sentence how to write the relevant knowledge, the content is detailed and easy to understand, the operation is simple and fast, has a certain reference value, I believe that everyone after reading this SQL left connection statement how to write the article will have a harvest, let's take a look at it.

In relational databases, tables are usually associated with each other in some way, allowing their information to be written only once in the entire database. Then, when you need to analyze the data, you need to combine the information from these related tables.

To do this in SQL, you can use the join statement. This LEFT JOIN statement is one of the various JOIN statements available. When you use it to join two tables, it retains all rows of the first table (the left table), even if there is no corresponding match on the second table.

You can use JOIN to join two tables table_1 and table_2 in a SELECT query, as follows:

SELECT columnsFROM table_1LEFT OUTER JOIN table_2ON relation;SELECT columnsFROM table_1LEFT JOIN table_2ON relation

First you write down which columns will appear in the join table. You can specify which table the column belongs to by adding the table name before the column name. If the names of some columns, such as table_1.column_1 and table_2.column_1, are associated with SELECT.

Then you can write the name of the first table as FROM table_1.

After that, you write the name of the second table LEFT OUTER JOIN table_2or LEFT JOIN table_2 (omit the OUTER keyword).

Finally, you need to write a relationship to match rows, such as ON table_1.column_A = table_2.column_B. Usually the relationship is through id, but it can be any column.

SQL LEFT JOIN example

Suppose you have a database of books with two tables, one for books and the other for authors. To avoid repeating all the author information for each book, the information is in its own table and the book has only that author_name column.

BOOK_IDTITLEAUTHOR_NAMEPUBL_YEAR1Uno, nessuno e centomila Luigi Pirandro 19262Il visconte dimezzato Italo Calvino 19523Le tigri di Mompracem Emilio Salgary 19004Il giorno della civetta Leonardo Summer 19615A ciascuno il suo Leonardo Summer 19666Il fu Mattia Pascial Luigi Pirandro 19047I Malavoglia

Giovanni Verga 1881AUTHOR_IDNAMEYEAR_OF_BIRTHPLACE_OF_BIRTHTRVIA1 Luigi Pirandro 1867 Agrizento 1934 Nobel Prize for Literature 2 Giovanni Verga 1840 Vizzini was a senator of the Kingdom of Italy from 1920 to 1922 3 Italo Swevo 1861 Trieste's real name is Aron Hector Schmitz4 Cesare Pavese 1908 St. Stefan Nobelbo null5 Giuseppe Tomasi Di Lampedusa 1896 Balle Mo was Prince Lampedusa from 1934 to 1957.

We can connect the two tables according to the author's name. Using the books table as the left table, you can write the following code to join them together:

SELECT books.title AS book_title, books.publ_year, books.author_name, authors.year_of_birth, authors.place_of_birth FROM books LEFT JOIN authors ON books.author_name = authors.name

Let's break it down.

In the first row, you can select the columns to display in the final table. It is also the place to determine whether certain columns have different names in the result table, using ASlike with books.title AS book_title.

The second row, FROM books, represents the first table to consider, also known as the left table.

Then the third line, LEFT JOIN authors, indicates the other tables to consider.

ON books.author_name = authors.name says to use row books.author_name and matching table authors.name.

After this query, you will get the following table, where rows that do not get information from the author table only display NULL.

BOOK_NAMEPUBL_YEARAUTHOR_NAMEYEAR_OF_BIRTHPLACE_OF_YEARUno, nessuno e centomila1926 Luigi Pirandro 1867 Agresto Il visconte dimezzato1952 Italo Calvino nullnull

Le tigri di Mompracem1900 Emilio Salgary null

Null

Il giorno della civetta1961 Leonardo Summer null

Null

A ciascuno il suo1966 Leonardo Summer null

Null

Yves Matia Pascal 1904 Luigi Pirandro 1867 Agrizin entrusted me to Malavolia 1881 Giovanni Vega 1840 Vizzini

Note that authors that are not in the books table are not in this join table. This is because, as I said before, only irrelevant rows in the left table (in this case, books) are retained, not the rows in the right / second table.

A more complex example of LEFT JOIN

Let's take a look at another way that JOIN can be used in conjunction with other SQL functions for data analysis.

You may want to see the number of books per author that exist in the database. You can do this using the following query:

SELECT authors.name AS author_name, SUM (CASE WHEN books.title LIKE'% 'THEN 1 ELSE 0 END) as number_of_books FROM authors LEFT JOIN books ON books.author_name = authors.name GROUP BY authors.name ORDER BY number_of_books DESC; code decomposition

Line 1: SELECT lists the required columns in the results table.

Line 2: SUM is an aggregate function used in conjunction with GROUP BY. The values of the rows grouped together are then added.

Lines 3-7: you use the CASE statement to get different results based on conditions. In this case, if a line contains the title of the book, it is counted as 1, otherwise it is counted as 0. Here we use LIKE to check whether the cell contains any characters.

Line 8: this gives the name of the column that number_of_books created for SUM.

Line 9: the left / first table in this example is authors.

Line 10: in this example, the right / second table is books.

Line 11: this joins the two tables with the author's name.

Line 12: rows are grouped by author name-all rows with the same value in this column will be represented by a row.

Line 13: we use order by to sort the number of books in descending order.

The query will provide you with the following table. Note that you can only see the authors in the authors table here. The authors mentioned in the books table without entries in the authors table do not exist here. This is the result of the fact that irrelevant rows in the books table are not retained.

If the authors table is updated to include all the authors mentioned in the books table, it looks like this:

AUTHOR_NAMENUMBER_OF_BOOKS Luigi Pirandro 2 Giovanni Verga 1 Cesare Pawese 0 Giuseppe Tomasi Di Lampedusa 0 Italo Svevo 0AUTHOR_IDNAMEYEAR_OF_BIRTHPLACE_OF_BIRTHTRIVA1 Luigi Pirandero 1934 Nobel Prize for Literature 2 Giovanni Verga 1840 Vizzini served as senator of the Kingdom of Italy 3 Italo Swayne from 1920 to 1922 Wo 1861 Trieste's real name is Aron Hector Schmitz4 Cesare Pawese 1908 San Stefan Nobelbo nulll5 Giuseppe Tomasi di Lampedusa 1896 Palermo served as Prince Lampedusa from 1934 to 1957 Prince Italo Calvino 1923 Santiago de Las Vegas nulll

7 Emilio Salgary 1862 Verona nulll

8 Leonardo summer 1921 la Kalmuto nulll

Then the table in the above query will actually give the number of books for all authors.

AUTHOR_NAMENUMBER_OF_BOOKS Leonardo Summer 2 Luigi Pirandro 2 Emilio Salgary 1 Giovanni Villega 1 Giovanni Vega 1 Cesare Pawese 0 Giuseppe Tomasi Di Lampedusa 0 Italo Swervo 0 this is the end of the article on "how to write SQL left join sentences". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to write the left link sentence of SQL". If you want to learn more, you are welcome to 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.