Q.73 Select Example using JOIN,AS,CROSS JOIN,JOIN ON,OUTER JOIN, Compound JOIN,Self JOIN. Ans:-
- First create table.
- CREATE TABLE x ( a, b );
INSERT INTO x VALUES ( 1, ‘Alice’ );
INSERT INTO x VALUES ( 2, ‘Bob’ );
INSERT INTO x VALUES ( 3, ‘Charlie’ ); - CREATE TABLE y ( c, d );
INSERT INTO y VALUES ( 1, 3.14159 );
INSERT INTO y VALUES ( 1, 2.71828 );
INSERT INTO y VALUES ( 2, 1.61803 ); - CREATE TABLE z ( a, e );
INSERT INTO z VALUES ( 1, 100 );
INSERT INTO z VALUES ( 1, 150 );
INSERT INTO z VALUES ( 3, 300 );
INSERT INTO z VALUES ( 9, 900 ); - select * from x;
- | a | b |
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie| - select d, d*d AS dSquared FROM y;
AS
|d |dSquared
|3.14159 |9.8695877281
|2.71828 |7.3890461584
|1.61803 |2.6180210809here y table create time column name is c and d. after using select command using AS change the name.
JOIN,CROSS JOIN
- select * from x join y;
select * from x cross join y;
select * from x,y; - |a |b |c |d
|1 |Alice |1 |3.14159
|1 |Alice |1 |2.71828
|1 |Alice |2 |1.61803
|2 |Bob |1 |3.14159
|2 |Bob |1 |2.71828
|2 |Bob |2 |1.61803
|3 |Charlie |1 |3.14159
|3 |Charlie |1 |2.71828
|3 |Charlie |2 |1.61803JOIN…ON
- select * from x join y on a=c;
|a| b |c| d
|1| Alice |1| 3.14159
|1| Alice |1| 2.71828
|2| Bob |2| 1.61803 - select * from x join z on x.a =z.a;
|a| b |a| e
|1| Alice |1| 100
|1| Alice |1| 150
|3| Charlie |3| 300OUTER JOIN
- select * from x left outer join z using (a);
|a| b |e
|1| Alice |100
|1| Alice |150
|2| Bob |[NULL]
|3| Charlie |300Compound JOIN
- select * from x join y on x.a = y.c left outer join z on y.c = z.a;
|a| b |c| d | a| e
|1| Alice |1| 3.14159| 1| 100
|1| Alice |1| 3.14159| 1| 150
|1| Alice |1| 2.71828| 1| 100
|1| Alice |1| 2.71828| 1| 150
|2| Bob |2| 1.61803| [NULL]| [NULL]Self JOIN
- select * from x as x1 join x as x2 on x1.a+1=x2.a;
|a| b |a| b
|1| Alice |2| Bob
|2| Bob |3| Charlie
swamios.com 