SQL Joins
Joins in SQL
SQL stands for structured query language and is required for storing, sorting, manipulating and retrieving data that are stored in relational databases. All the major RDMS formats like MySQL, MS Access, Sybase, Oracle, Informix, Postgres, and SQL Server use SQL as the standard database language.
SQL is also an ANSI standard language and there are many variations of this programming language.
What is the SQL process?
You should know the best method to execute an SQL command over any DBMS system and to interpret the specific task. Important components included in the process are:
- Optimization engines
- Query engines
- Query dispatcher
- Classic Query engine
Advantages of SQL
There are several advantages of SQL commands some of which are discussed below:
No need for coding
SQL command doesn’t need any coding and so it is very simple to manage. The database becomes extremely simple when you operate with fewer codes
Speed
SQL is indeed a high-speed coding language and as such, it can efficiently retrieve large amounts of information from databases. This helps to speed up many operations and get information faster.
Easy to Carry
This program is extremely portable across all systems and can be easily carried over in laptops, PC’s and servers and even runs on high-end android applications.
Multiple views
Different views of the database structure can be achieved when you use the command.
Interactive
SQL is a domain language that can easily communicate with the databases. You can receive answers to complex queries in a matter of seconds.
What are SQL Joins?
SQL joins are statements that are used to combine data or rows from one or more tables based on the common field of operation. These are highly specialized commands which can help you with the database structures across many platforms. The different types of joins are:
- Inner join
- Left join
- Right join
- Full join
| Roll no | Name | Address | Phone | Age |
|---|---|---|---|---|
| 1 | Harsh | Delhi | Xxx | 18 |
| 2 | Pratik | Bihar | Xxx | 19 |
| 3 | Riyanka | Siliguri | Xxx | 20 |
| 4 | Deep | Ramnagar | Xxx | 18 |
| 5 | Saptarshi | Kolkata | Xxx | 19 |
| 6 | Dhanraj | Barabajar | Xxx | 20 |
| 7 | Rohit | Balurghat | Xxx | 18 |
| 8 | Niraj | Alipur | Xxx | 19 |
| Course ID | Roll no |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 4 |
| 1 | 5 |
| 4 | 9 |
| 5 | 10 |
| 4 | 11 |
Inner Join
This is the simplest of joins in SQL and the keywords select all rows from both of the tables meeting all the conditions. The results sets will be created by the keywords combining all the rows from both the tables matching the conditions so that the values of the common fields will be the same.
| Course id | Name | Age |
|---|---|---|
| 1 | Harsh | 18 |
| 2 | Pratik | 19 |
| 2 | Riyanka | 20 |
| 3 | Deep | 18 |
| 1 | Saptarshi | 19 |
Left Join
This join returns all the rows on the table from the left side of the joins and matches the rows against the tables on the right side of the joins. The rows not matching on the right side, the result will contain null. This is also known as the left outer join.
| Name | Course id |
|---|---|
| Harsh | 1 |
| Pratik | 2 |
| Riyanka | 2 |
| Deep | 3 |
| Saptarshi | 1 |
| Dhanraj | Null |
| Rohit | Null |
| Niraj | Null |
Right Join
Right Join is identical with the left join and returns all the rows of the table on the right side of the join and the matching rows for the table on the left side of the join. The rows which don’t match the results will contain null. This is also known as the right outer join.
| Name | Course Id |
|---|---|
| Harsh | 1 |
| Pratik | 2 |
| Riyanka | 2 |
| Deep | 3 |
| Saptarshi | 1 |
| Null | 4 |
| Null | 5 |
| Null | 4 |
Full Join
Full joins create the results by a combination of the right and left joins. The results will contain all the rows matching from both the tables. The rows not matching will return a corresponding null value.
| Name | Course Id |
|---|---|
| Harsh | 1 |
| Pratik | 2 |
| Riyanka | 2 |
| Deep | 3 |
| Saptarshi | 1 |
| Dhanraj | Null |
| Rohit | Null |
| Niraj | Null |
| Null | 9 |
| Null | 10 |
| Null | 11 |
Cross Join
Cross joins are another simple form of joins that matches each row from one database table to the rows of another. It gives a combination of each row of the first table with all the records in the second table.
Natural Joins
Natural joins are joining operations which gives you an output based on the columns on both the tables between which this operation must be implemented. The main difference between the inner join and natural join is on the number of columns returned.
Hash Join
Hash joins are also a type of join which are used to join large tables where you want the greatest number of rows. The Hash join algorithm is a two-step process with a build phase to create an in-memory hash index on the left side input and a probe phase to go through the right-side input and to find the matches using the index created in the above-mentioned steps.
Why Should you use Joins?
It is a common question that why should you use joins when you can run queries for the same programs. If you have some experience in database programming you can run queries one by one. Well, you can do that but joins make your task simpler by helping you to run one single query with any search criteria. MySQL achieves better performance with joins as it uses indexing. Server reducing time is achieved only when you use joins. So, it is clear that you achieve better results when you use joins.
So, now you have a basic idea about SQL and the different types of joins used in the programming. You have also gone through many examples of writing queries and the corresponding output returned. It will help you write your own queries and help you to perform large database operations. So, as you dive deeper into SQL programming you will have a solid foundation on which you can proceed further.
