Learn Python

Learn Data Structure & Algorithm

Learn Numpy

Learn Pandas

Learn Matplotlib

Learn Seaborn

Learn Statistics

Learn Math

Learn MATLAB

Learn Machine learning

Learn Github

Learn OpenCV

Learn Deep Learning

MySQL Introduction

MySQL Setup

MySQL Operators

MySQL Basic Works

MySQL Create Table

MySQL insert and get values

MySQL Condition

MySQL GROUP BY

MySQL Case

MySQL sorting and calculate avg,sum,count

MySQL Update

MySQL Time

MySQL String

MySQL Rollback & Commit

MySQL Join and Concatenate

MySQL Index

MySQL Date

MySQL View

MYSQL With Python

Learn MongoDB

Learn Web scraping

Learn Excel

Learn Power BI

Learn Tableau

Learn Docker

Learn Hadoop

Everything about sql join

List of different join methods:

1. INNER JOIN: This method returns only the matching records from the both tables.
2. LEFT JOIN: This method returns all the records from the left table, and only the matched records from the right table.
3. RIGHT JOIN: This method returns all the records from the right table, and only the matched records from the left table.
4. FULL OUTER JOIN: This method returns all the records when there is a match in either left table or right table.

Let's see in the example:

INNER JOIN

Syntax:
SELECT tableName.colname, ... tableName.colname,
/*
Write those column names which you want to join from both table
*/
FROM table1Name
INNER JOIN table2Name
ON table1Name.foreign_key_ColName=table2Name.primary_key_ColName;

LEFT JOIN

Syntax:
SELECT tableName.colname, ... tableName.colname,
/*
Write those column names which you want to join from both table
*/
FROM table1Name
LEFT JOIN table2Name
ON table1Name.foreign_key_ColName=table2Name.primary_key_ColName;
ORDER BY tableName.ColName_to_sort;
/*
In left join it is better to sort the joined table because the table can have any order. So sort the table and bring the joined table into a specific order using ORDER BY and ColName_to_sort means that column name according to which you want to sort and tableName is that column table name.
*/

RIGHT JOIN

Syntax:
SELECT tableName.colname, ... tableName.colname,
/*
Write those column names which you want to join from both table
*/
FROM table1Name
RIGHT JOIN table2Name
ON table1Name.foreign_key_ColName=table2Name.primary_key_ColName;
ORDER BY tableName.ColName_to_sort;
/*
In RIGHT JOIN it is better to sort the joined table because the table can have any order. So sort the table and bring the joined table into a specific order using ORDER BY and ColName_to_sort means that column name according to which you want to sort and tableName is that column table name.
*/

FULL OUTER JOIN

Syntax:
SELECT tableName.colname, ... tableName.colname,
/*
Write those column names which you want to join from both
*/
FROM table1Name
FULL OUTER JOIN table2Name
ON table1Name.foreign_key_ColName=table2Name.primary_key_ColName;

Let's see example:

First table:
The second table name is city and columns are uid and city. Here uid is the primary key column.

Second table:
Table name is personal_doc and the columns are uid, name, percentage, age, gender, and city. Here uid is the foreign key column.

INNER JOIN:
SELECT
personal_doc.id, personal_doc.name, personal_doc.age, personal_doc.gender, city.city
FROM personal_doc
INNER city
ON personal_doc.uid=city.uid;

LEFT JOIN:
SELECT
personal_doc.id, personal_doc.name, personal_doc.age, personal_doc.gender, city.city
FROM personal_doc
LEFT JOIN city
ON personal_doc.uid=city.uid;
ORDER BY personal_doc.uid

RIGHT JOIN:
SELECT
personal_doc.id, personal_doc.name, personal_doc.age, personal_doc.gender, city.city
FROM personal_doc
RIGHT JOIN city
ON personal_doc.uid=city.uid;
ORDER BY personal_doc.uid

FULL OUTER JOIN:
SELECT
personal_doc.id, personal_doc.name, personal_doc.age, personal_doc.gender, city.city
FROM personal_doc
FULL OUTER JOIN city
ON personal_doc.uid=city.uid;

You can also use "WHERE" to perform join:
Suppose there is a city name New York.
To see values for New York:

SELECT
personal_doc.id,personal_doc.name,personal_doc.age,personal_doc.gender,city.city
FROM personal_doc
FULL OUTER JOIN city
ON personal_doc.uid=city.uid;
WHERE city.city="New York"

How perform sorting while join tables?

SELECT
personal_doc.id,personal_doc.name,personal_doc.age,personal_doc.gender,city.city
FROM personal_doc
JOIN city
ON personal_doc.uid=city.uid;
WHERE city.city="New York"
ORDER BY personal_doc.name

How to join more than two tables?

Syntax:
SELECT colName, colName...colName
/*
Here write all the column names which you want to join from all the table.
*/
FROM table1
FULL OUTER JOIN table2
ON table1Name.foreign_key_ColName=table2Name.primary_key_ColName
FULL OUTER JOIN table3
ON table1Name.foreign_key_ColName=table3Name.primary_key_ColName;

/*
Here table two and three column that is used to join, must be PRIMARY key column and table1 column must by the FOREIGN key.
*/

Let's see example:

First table:
The table name is personal_doc and the columns are id, name, percentage, age, gender, and city. Here city is the foreign key column
Second table:
The second table named city and columns are cid and city. Here cid is the primary key column.
Third table:
Third table named is other_doc and there you have columns like roll and department. Here roll is the PRIMARY key column

SELECT
personal_doc.id,personal_doc.name,personal_doc.age,personal_doc.gender,city.city
FROM personal_doc
JOIN city
ON personal_doc.city=city.cid
JOIN other_doc
ON personal_doc.city=other_doc.roll;

Let's use WHERE:

SELECT
personal_doc.id,personal_doc.name,personal_doc.age,personal_doc.gender,city.city
FROM personal_doc
JOIN city
ON personal_doc.city=city.cid
JOIN other_doc
ON personal_doc.city=other_doc.roll
WHERE city.city="New York";

How to concat multiple columns?

Syntax:
select concat(here we write those column name which we want to concat) as new_concat_column_name from table_name;

Example:
select concat(book,price) as Book&price from books_store_1;
select concat(book,price) as Book," ",&," ",price from books_store_1;

CodersAim is created for learning and training a self learner to become a professional from beginner. While using CodersAim, you agree to have read and accepted our terms of use, privacy policy, Contact Us