forked from miguelgfierro/codebase
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathjoins.sql
More file actions
74 lines (67 loc) · 2.92 KB
/
Copy pathjoins.sql
File metadata and controls
74 lines (67 loc) · 2.92 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
-- INNER JOIN: Select values as the intersection of the two tables, i.e. rows they have common in table1 and table1
SELECT table1.column1, table2.column2
FROM table1 INNER JOIN table2
ON table1.common_field = table2.common_field;
-- Example:
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
INNER JOIN TableB
ON TableA.id = TableB.id2;
-- Result:
firstName lastName age Place
..............................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
-- LEFT JOIN: will give all selected rows in TableA, plus any common selected rows in TableB.
SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
-- Example:
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
LEFT JOIN TableB
ON TableA.id = TableB.id2;
-- Result:
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
new abc NULL NULL
-- RIGHT JOIN: will give all selected rows in TableB, plus any common selected rows in TableA.
SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
-- Example:
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
RIGHT JOIN TableB
ON TableA.id = TableB.id2;
-- Result:
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
NULL NULL 24 chennai
-- FULL JOIN: It is same as union operation, it will return all selected values from both tables.
SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
--Example:
SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place
FROM TableA
FULL JOIN TableB
ON TableA.id = TableB.id2;
-- Result:
firstName lastName age Place
...............................................................................
arun prasanth 24 kerala
ann antony 24 usa
sruthy abc 25 ekm
new abc NULL NULL
NULL NULL 24 chennai