MYSQL | Important Questions & Functionality

MYSQL Question

# Join In MYSQL

4 types of join in SQL:

(i) (INNER) JOIN: Returns records that have matching values in both tables

Syntax:

SELECT columns_from_both_tables
FROM table1
JOIN table2
ON table1.column1 = table2.column2

Example :

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

Join Multiple table:

SELECT Customers.first_name, Orders.item, Shippings.status
FROM Customers
JOIN Orders ON Customers.customer_id = Orders.customer_id
JOIN Shippings ON Customers.customer_id = Shippings.customer;

(ii) LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table

Syntax:

SELECT columns_from_both_tables
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column2

Example:

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

(iii) RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table

Syntax:

SELECT columns_from_both_tables
FROM table1
RIGHT JOIN table2
ON table1.column1 = table2.column2

Example:

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

(iv) FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Syntax:

SELECT columns
FROM table1
FULL JOIN table2
ON table1.column1 = table2.column2;

Example:

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

(v) CROSS JOIN: Returning all possible combinations of all rows. if table1 have 4 records and table2 have 3 records, reult table have 4*3 = 12 records

Syntax:

SELECT column1, column2, ...
FROM table1
CROSS JOIN table2;

Example:

SELECT * 
FROM Customers
CROSS JOIN Orders;

# Get the count of ids form user table

$result = mysql_query("SELECT COUNT('id') AS `count` FROM users");

#Use Multiple database using SQL

# Datatypes in MySQL

# Constraints (Restrictions) in MySQL