SQL Joins
Introduction
In the previous tutorial, we have discussed the WHERE clause with different operators. In this tutorial, we shall look into the SQL joins and different concepts related to it.
The most useful operation in SQL is joining the two different tables with different data retrievals on the one go. SQL joins are the most important operations that one can perform on different tables using the SELECT command as discussed in the previous tutorial. The better understanding of SQL joins and the syntax of it is important as a part of SQL learning.
Before moving onto the concepts of SQL joins, it is important to understand the relational tables and relational database design. The best way to understand it is by considering a real-world example.
Relational Database
Let us consider a database design containing a product catalogue, that includes the information related to the product, price of it and the vendor who manufactures it, address, the contact information of the vendor and much more. If there are multiple catalogs produced by the same vendor, then the similar information of vendor must be repeated in multiple rows.
If the same data is repeated multiple times, it consumes high storage space and a good time for retrieval.
If one vendor information changes for any reason then the following has to be updated in all the respective rows.
If the data is repeated then there will be high chances of errors or mistakes as the similar data is entered for many times.
Finally, the repetition of similar data multiple times is not a good thing. This is the major principle for relational databases. Relational databases are helpful to split into the multiple tables with different data types, where all the tables are connected with some sort of relationship.
Here, in this example two tables, one for catalog and other for vendor information. The primary can be the vendor ID to have the relation between the two tables consistently. This design will overcome the limitations as discussed above. By this way, the data can be stored effectively and manipulated easily with greater scalability.
Joins
From the above-discussed scenario, the data is stored in multiple tables to have high scalability. To retrieve the data from the different tables, join mechanism can be applied to the tables with the SELECT command. Using the syntax of the join, multiple tables can be joined to return a single set of output. Join will combine all the rows of the table on the fly and show the output.
Syntax of Join
SELECT Col1 Tab 1, Col2 Tab 1, Col1 Tab2
From Tab1, Tab2
WHERE Tab1.primary_key = Tab2.primary_keyExample
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
Output
vend_name prod_name prod_price
——— ———- ———-
Doll House Inc. Fish bean bag toy 3.4900
Doll House Inc. Bird bean bag toy 3.4900
Doll House Inc. Rabbit bean bag toy 3.4900
Bears R Us 8 inch teddy bear 5.9900
Bears R Us 12 inch teddy bear 8.9900
Bears R Us 18 inch teddy bear 11.9900
Doll House Inc. Raggedy Ann 4.9900
Fun and Games King doll 9.4900
Fun and Games Queen doll 9.4900
Here, the two different tables are joined and they are retrieved as the output on the fly with the required fields, as mentioned in the syntax of SQL Join.
This is all about the basic introduction about the SQL Joins and the syntax of SQL joins. In the upcoming tutorials, we shall discuss the different operations using the SQL joins. For any sort of doubts or questions in this tutorial, you can reach us through the comment box.
Related Tutorials