टेबल्स जोड़ना
SQL Server परिचय
John MacKintosh
Instructor
Relational Databases
Primary Keys
- Primary keys: किसी टेबल की हर row को uniquely पहचानते हैं
+-----------+-------------------+
| artist_id | name |
|-----------+-------------------|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+-----------+-------------------+
- Primary key:
artist_id
+----------+-------------------------+-----------+
| album_id | title | artist_id |
|----------+-------------------------+-----------|
| 1 | For Those About To Rock | 1 |
| 2 | Balls to the Wall | 2 |
| 3 | Restless and Wild | 2 |
| 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 |
+----------+-------------------------+-----------+
- Primary key:
album_id artist_idके बारे में क्या?
Foreign keys
artistटेबल
+-----------+-------------------+
| artist_id | name |
|-----------+-------------------|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+-----------+-------------------+
albumटेबल
+----------+-------------------------+-----------+
| album_id | title | artist_id |
|----------+-------------------------+-----------|
| 1 | For Those About To Rock | 1 |
| 2 | Balls to the Wall | 2 |
| 3 | Restless and Wild | 2 |
| 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 |
+----------+-------------------------+-----------+
artist_id:artistकी foreign key
album और artist को जोड़ना
artistटेबल
+-----------+-------------------+
| artist_id | name |
|-----------+-------------------|
| 1 | AC/DC |
| 2 | Accept |
| 3 | Aerosmith |
| 4 | Alanis Morissette |
| 5 | Alice In Chains |
+-----------+-------------------+
- AC/DC का
artist_id= 1 है
albumटेबल
+----------+-------------------------+-----------+
| album_id | title | artist_id |
|----------+-------------------------+-----------|
| 1 | For Those About To Rock | 1 |
| 2 | Balls to the Wall | 2 |
| 3 | Restless and Wild | 2 |
| 4 | Let There Be Rock | 1 |
| 5 | Big Ones | 3 |
+----------+-------------------------+-----------+
- Row 1 और 4 में
artist_id= 1 है
album और artist को जोड़ना
+----------+-------------------------+-----------+-------------+
| album_id | title | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1 | For Those About To Rock | 1 | AC/DC |
| 4 | Let There Be Rock | 1 | AC/DC |
+----------+-------------------------+-----------+-------------|
albumटेबल से album details लौटाएँartistटेबल से संबंधित artist details लौटाएँartist_idकॉलम से join किया गया
INNER JOIN
SELECT
album_id,
title,
album.artist_id,
name AS artist_name
FROM album
INNER JOIN artist ON artist.artist_id = album.artist_id
WHERE album.artist_id = 1;
+----------+-------------------------+-----------+-------------+
| album_id | title | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1 | For Those About To Rock | 1 | AC/DC |
| 4 | Let There Be Rock | 1 | AC/DC |
+----------+-------------------------+-----------+-------------|
INNER JOIN सिंटैक्स
SELECT
table_A.columnX,
table_A.columnY,
table_B.columnZ
FROM table_A
INNER JOIN table_B ON table_A.foreign_key = table_B.primary_key;
SELECT
album_id,
title,
album.artist_id,
name AS artist_name
FROM album
INNER JOIN artist on artist.artist_id = album.artist_id;
+----------+---------------------------------------+-----------+
| album_id | title | artist_id | artist_name |
|----------+---------------------------------------+-----------|
| 1 | For Those About To Rock | 1 | AC/DC |
| 4 | Let There Be Rock | 1 | AC/DC |
| 2 | Balls To The Wall | 2 | Accept |
| 3 | Restless and Wild | 2 | Accept |
+----------+---------------------------------------+-----------+
albumऔरartistके बीच सभी मैच के सभी संयोजन लौटाता है
Multiple INNER JOINS
SELECT
table_A.columnX,
table_A.columnY,
table_B.columnZ, table_C columnW
FROM table_A
INNER JOIN table_B ON table_B.foreign_key = table_A.primary_key
INNER JOIN table_C ON table_C.foreign_key = table_B.primary_key;
चलो कुछ टेबल्स जोड़ते हैं!
SQL Server परिचय
