Untitled

class_connect

Oct 15th, 2024

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

text 2.21 KB | None | 0 0

raw download clone embed print report

The SQL Join Clause
The SQL Join clause is used to combine data from two or more
tables in a database.
The part of the Join clause that specifies the columns on which
records from two or more tables are joined is known as join-
predicate. This predicate is usually specified along with the ON
clause and uses various comparison operators such as, <, >, <>,
<=, >=, !=, BETWEEN, LIKE, and NOT etc. We can also connect
multiple join predicates with logical operators AND, OR, and NOT.
Syntax
SELECT column_name(s)
FROM table1
JOIN table2;
Example
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );
The CUSTOMERS table will be created as follows −
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00
Following is another table ORDERS which contains the order
details made by the customers.
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);
The ORDERS table will be created as follows −
OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00
Following query performs the join operation on the tables
CUSTMERS and ORDERS –
SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;
Output
ID NAME AGE AMOUNT
3 Kaushik 23 3000
3 Kaushik 23 1500
2 Khilan 25 1560
4 Chaitali 25 2060

Add Comment

Please, Sign In to add comment