Introduction
SQL Joins can be a tricky concept for beginners to master. If you haven't studied programming before, you may have a hard time understanding what combinations are in SQL and the different types of unions.
But as an aspiring or data science professional, you should have a solid understanding of what SQL joins are and how they work. Créame, you will use it a lot if the data science industry quickly retrieves and manipulates the data present in different tables.
In this article, i will show that sql joins are really easy to learn. We will first understand what SQL joins are and then we will look at the four different types of joins that you will need to master..
Do you want to learn the basics of what SQL is and how it can be applied in data science? Look at the popular course SQL for data science.
What are SQL Joins?
Let's first answer the million dollar question before looking at the different types of joins in SQL.
I'll take an intuitive example to explain what SQL joins are. Consider these two collections:
Let's say the blue circle represents the set of all children (KIDS) and the gray represents the set of people who love to see Messi play (MESSI). How would you proceed if we wanted the set of all the guys who love to see Messi play??
There is a very procedural way to approach this problem:
- First, select all the different identifiers from Messi's table that represent the inner query below
- Take each ID from the Boys chart and compare it to this set.
- If the id matches any of them, then generate that row from Boys table
This is quite similar to the concept of a 'for loop’ and it is called sub-selection in SQL.
SELECTThe command "SELECT" is fundamental in SQL, used to query and retrieve data from a database. Allows you to specify columns and tables, filtering results using clauses such as "WHERE" and ordering with "ORDER BY". Its versatility makes it an essential tool for data manipulation and analysis, facilitating the obtaining of specific information efficiently.... * FROM BOYS WHERE"WHERE" is a term in English that translates as "where" in Spanish. Used to ask questions about the location of people, Objects or events. In grammatical contexts, it can function as an adverb of place and is fundamental in the formation of questions. Its correct application is essential in everyday communication and in language teaching, facilitating the understanding and exchange of information on positions and directions.... id IS IN (SELECT DISTINCTThe word "DISTINCT" in English it translates into Spanish as "different" O "different". In the field of programming and databases, especially in SQL, Used to remove duplicates in query results. When applying the DISTINCT clause, only the unique values of a dataset are obtained, which facilitates the analysis and presentation of relevant and non-redundant information.... id FROM MESSI);
But in SQL, there is another way to approach this problem.
To begin to understand combinations, we must first have a different perspective on what we really want. In established terminology: we want the intersection by BOYS and MESSI. In graphic terms, this is expressed as:
We are interested in the celestial part, truth? This part, o la indoor part (track), They are all the boys who love to see Messi. All we have to do now is express this in SQL:
SELECT * FROM BOYS
INNER JOIN"JOIN" is a fundamental operation in databases that allows you to combine records from two or more tables based on a logical relationship between them. There are different types of JOIN, as INNER JOIN, LEFT JOIN and RIGHT JOIN, each with its own characteristics and uses. This technique is essential for complex queries and more relevant and detailed information from multiple data sources.... MESSI
ON BOYS.id = MESSI.id;
Look what the (indoor) join ago? It couldn't be easier! This is the intuitive approach on how to understand the combinations.
Note: Venn diagrams are not applied directly to SQL because elements in collections (The tables) they are not identical. But because they refer to each other, we can use Venn diagrams to better understand the concept.
Different types of joins in SQL
Now. We will extend this to the big picture and learn about the different types of SQL joins. Consider the following sample tables:
1. INNER JOIN en SQL
This is what we covered in the previous section. Inner Joina "Inner Join" is an operation in databases that allows you to combine rows of two or more tables, based on a specific match condition. This type of join only returns rows that have correspondences in both tables, resulting in a result set that reflects only the related data. It is critical in SQL queries to obtain cohesive and accurate information from multiple data sources.... devuelve registros que tienen valores coincidentes en ambas tablas:
Let's see what the output is using the above example:
SELECT * FROM BOYS INNER JOIN MESSI ON BOYS.id = MESSI.id;
PRODUCTION:
As I mentioned before, inner join gives the intersection of two tables, namely, rows that are common in both tables.
2. RIGHT JOINT (external) and SQL
Suppose we want the identification and name of all the people who love to see Messi play. Obviously, there are many ways to write this query, but we will understand it with the help of unions.
Let's see what the output is:
SELECT * FROM BOYS RIGHT JOIN MESSI ON BOYS.id = MESSI.id;
PRODUCTION:
Can you find out what happened here? The right outer join gives us the rows that are common in both tables, as well as additional rows from Messi's table that are not present at the intersection. In other words, a right join returns all records from the right table and matching records from the left table.
3. LEFT (Outer) JOIN en SQL
Let's say we want the list of all the guys who love to watch Messi play and don't love to see Messi play wearing joins..
I want you to guess the final result before reading on.
SELECT * FROM BOYS LEFT JOIN MESSI ON BOYS.id = MESSI.id;
PRODUCTION:
The left outer join gives us the rows that are common in both tables, as well as additional rows from the Boys table that are not present in the intersection. In other words, a left join returns all records from the left table and matching records from the right table.
4. Complete union (EXTERIOR) and SQL
Finally, let's say we want the list of all people, including kids who love to watch Messi play.
I'm sure you already know the answer by now!!
SELECT * FROM BOYS FULL OUTER JOIN MESSI ON BOYS.id = MESSI.id;
PRODUCTION:
Perfect! A full outer join gives us the rows that are common in both tables, as well as additional rows from both tables that are not present at the intersection. We get all the records when there is a match in the left or right table.
Final notes
Want to learn how SQL can be used in data science? I highly recommend taking a look at this amazing course: Structured query language (SQL) for data science.
If you have any questions or comments about this article, let me know in the comment section below and I'll be happy to connect with you.
