Total Page Preview: 000000000349
What is Join and it basic types in SQL in English and Hindi
Join and it basic types in English
In SQL, a join is a method used to combine rows from two or more tables based on a related column between them.
The SQL JOIN is a command clause that combines records from two or more tables in a database. It is a means of combining data in fields from two tables by using values common to each table. If you're working with databases, at some point in your work you will likely need to use SQL JOINs
The basic types of joins in SQL are:
1-INNER JOIN:
Inner join is the most common type of join used in SQL. It returns only the matched rows from both tables based on the specified condition. Here is an example:
Suppose you have two tables called "orders" and "customers" and you want to join them based on the customer_id column. The SQL query would be:
2-LEFT JOIN:
Left join is used to return all the rows from the left table and matching rows from the right table. If there are no matching rows in the right table, NULL values will be returned. Here is an example:
Suppose you have two tables called "orders" and "customers" and you want to join them based on the customer_id column but also include all orders regardless of whether or not there is a matching customer record. The SQL query would be:
3-RIGHT JOIN:
Right join is similar to left join, but it returns all the rows from the right table and matching rows from the left table. If there are no matching rows in the left table, NULL values will be returned. Here is an example:
Suppose you have two tables called "orders" and "customers" and you want to join them based on the customer_id column but also include all customers regardless of whether or not there is a matching order record. The SQL query would be:
4-FULL OUTER JOIN:
Full outer join returns all the rows from both tables, including the unmatched rows. If there are no matching rows in either table, NULL values will be returned. Here is an example:
Suppose you have two tables called "orders" and "customers" and you want to join them based on the customer_id column and include all unmatched rows from both tables. The SQL query would be:
Join and it basic types in Hindi
"Join" ek relational database management system (RDBMS) ka concept hai, jiske dwara hum multiple tables se data retrieve kar sakte hai. Join ke dwara, hum multiple tables ke columns ko combine kar sakte hai jisse humara data more meaningful ban jata hai. SQL mai, humein primarily 4 types ke joins available hote hai:
1-Inner Join:
Inner join, do tables ke beech mai common data ko retrieve karne ke liye use kiya jata hai. Inner join karne ke liye hum "JOIN" keyword ka use karte hai.
Example:
Consider kijiye ki humare paas ek "students" table hai jisme student_id aur student_name columns hai aur dusra table "courses" hai jisme course_id aur course_name columns hai. Ab hum inner join ka use karke dono tables ke beech mai common data retrieve karna chahte hai.
2-Left Join:
Left join, do tables ke beech mai common aur non-common data ko retrieve karne ke liye use kiya jata hai. Left join karne ke liye hum "LEFT JOIN" keyword ka use karte hai.
Example:
Consider kijiye ki humare paas ek "students" table hai jisme student_id aur student_name columns hai aur dusra table "courses" hai jisme course_id, course_name aur course_instructor columns hai. Ab hum left join ka use karke students table se courses table ko join karte hai, jisse humare paas courses table ke saare rows aa jayenge, chahe wo students table se match kare ya na kare.
3-Right Join:
Right join, left join ka opposite hai. Right join, do tables ke beech mai common aur non-common data ko retrieve karne ke liye use kiya jata hai. Right join karne ke liye hum "RIGHT JOIN" keyword ka use karte hai.
Example:
Consider kijiye ki humare paas ek "students" table hai jisme student_id aur student_name columns hai aur dusra table "courses" hai jisme course_id, course_name aur course_instructor columns hai. Ab hum right join ka use karke courses table se students table ko join karte hai, jisse humare paas students table ke saare rows aa jayenge, chahe wo courses table se match kare ya na kare.
Full Outer Join:
"Full outer join" ek SQL ka join prakar hai jiske dwara hum multiple tables ko combine kar sakte hai. Full outer join, inner join aur left/right outer join ka combination hota hai, jiske dwara hum dono tables ke common aur uncommon records ko display kar sakte hai.
Full outer join ka use karke hum dono tables ke saare records ko display kar sakte hai, aur agar koi record dusre table se match nahi karta hai to uski jagah NULL value display hota hai.
Example:
Thank You
About Author
Brijesh Kumar
Database Developer
I have more then 6 years Experience in Microsoft Technologies - SQL Server Database, ETL
Azure Cloud - Azure SQL Database, CosmosDB, Azure Data Factory, PowerBI, Web Job, Azure Function, Azure Storage, Web Apps, Powershall
and Database Migration On-Premise to Azure Cloud.
LinkedIn : https://www.linkedin.com