SQL

how to join tables in sql

JOINING 2 Tables in sql

SELECT X.Column_Name , Y.Column_Name2
FROM TABLES1_NAME X 
INNER JOIN TABLES2_NAME Y ON X.Primary_key = Y.Foreign_key;


--FOR EXAMPLE
--GET THE FIRST_NAME AND JOB_TITLE
--USE EMPLOYEES AND JOBS TABLE
--THE RELATIONSHIP IS JOB_ID

SELECT E.FIRST_NAME , J.JOB_TITLE
FROM EMPLOYEES E
INNER JOIN JOBS J ON J.JOB_ID = E.JOB_ID;

sql join

-- Rows with ID existing in both a, b and c
-- JOIN is equivalent to INNER JOIN
SELECT a.ID, a.NAME, b.VALUE1, c.VALUE1 FROM table1 a 
  JOIN table2 b ON a.ID = b.ID
  JOIN table3 c ON a.ID = c.ID
WHERE a.ID >= 1000;
-- ⇓ Test it ⇓ (Fiddle source link)

joins in sql

INNER JOIN:
is used when retrieving data from multiple
tables and will return only matching data.

LEFT OUTER JOIN:
is used when retrieving data from
multiple tables and will return
left table and any matching right table records.

RIGHT OUTER JOIN:
is used when retrieving data from
multiple tables and will return right
table and any matching left table records

FULL OUTER JOIN:
is used when retrieving data from
multiple tables and will return both
table records, matching and non-matching.



INNER JOIN :
SELECT select_list From TableA A
Inner Join TableB B
On A.Key = B.Key


LEFT OUTER JOIN :
SELECT select_list From TableA A
Left Join TableB B
On A.Key = B.Key

(where b.key is null)//For delete matching data



RIGTH OUTER JOIN :
SELECT select_list From TableA A
Right Join TableB B
On A.Key = B.Key


FULL JOIN :
SELECT select_list From TableA A
FULL OUTER Join TableB B
On A.Key = B.Key

Syntax for JOIN in SQL

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;


table1: First table.
table2: Second table
matching_column: Column common to both the tables.

sql join

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

SQL JOIN

SELECT Customers.customer_id, Customers.first_name, Orders.amount
FROM Customers
JOIN Orders
ON Customers.customer_id = Orders.customer;

joining tables in sql

SELECT orders.order_Number, customers.First_Name, customers.Last_Name, customers.Address
FROM orders
INNER JOIN Customers ON orders.customers_id=customers.id;

join sql

#INNER JOIN: Intersection between 2 tables
SELECT *
FROM A
INNER JOIN B ON A.key = B.key

#LEFT JOIN: left table with the intersection joined to the right table
SELECT *
FROM A
LEFT JOIN B ON A.key = B.key

#LEFT JOIN(without intesection): left table without the intersection joined
#								to the right table
SELECT *
FROM A
LEFT JOIN B ON A.key = B.key
WHERE B.key IS NULL

#RIGHT JOIN: right table with the intersection joined to the left table
SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key

#RIGHT JOIN(without intesection): right table without the intersection joined
#								to the left table
SELECT *
FROM A
RIGHT JOIN B ON A.key = B.key
WHERE A.key IS NULL # there seems to be a mistake on the site (B changed to A)

#FULL JOIN: union of 2 table
SELECT *
FROM A
FULL JOIN B ON A.key = B.key

#FULL JOIN(without intesection): union of two table without the intersection
SELECT *
FROM A
FULL JOIN B ON A.key = B.key
WHERE A.key IS NULL
OR B.key IS NULL

sql Join

LEFT JOIN
All rows from the left table will be returned, even if there's no matching row in the right table.

RIGHT JOIN
All rows from the right table will be returned, even if there's no matching row in the left table.

INNER JOIN
Only returns rows where there's a matching row in both tables.

sql joins explained

1
2
3
SELECT Employee.EmpID, Employee.EmpFname, Employee.EmpLname, Projects.ProjectID, Projects.ProjectName
FROM Employee
INNER JOIN Projects ON Employee.EmpID=Projects.EmpID;