⚡Created Random data and experimented with different JOINS.
👉Different Types of SQL JOINs to fetch data:
INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN
SELF JOIN
🔹Used MYSQL Commands and Data types:
CREATE TABLE- creates a new table.
SELECT — is used to select data from a database.
INSERT INTO — inserts new data into a database.
LEFT OUTER JOIN (or) LEFT JOIN— this type of Join would fetch all the rows (including NULL values) from the table which is on the left side of the JOIN query.
RIGHT OUTER JOIN (or) RIGHT JOIN— in this type of Join all the records that do not match from the right table are returned with NULL values on the columns for the left side table.
UPDATE — the statement is used to modify the existing records in a table.
ALTER TABLE— the statement is used to add, delete, or modify columns in an existing table.
CROSS JOIN — It produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table.
SELF JOIN — It is a join in which a table is joined with itself especially when the table has a FOREIGN KEY which references its PRIMARY KEY.
SET command — is used with UPDATE to specify which columns and values should be updated in a table.
WHERE Clause — is used to filter records.
INT — Integer numerical (no decimal).
VARCHAR — data type stores variable-length character strings.
NOT NULL — If you do not want a column to have a NULL value, then you need to define such a constraint on the column specifying that NULL is now not allowed for a specific column.
A PRIMARY KEY is a field in a table that uniquely identifies each row/record in a database table.
AUTO INCREMENT — allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
INNER JOIN — selects records that have matching values in both tables.
👉OUTER JOIN is used to retrieve all records from tables, even for those records with no matching value in the other table based on the JOIN condition. In such cases, it returns NULL as the value for the missing columns.
🔷There are two Outer Joins:
LEFT OUTER JOIN
RIGHT OUTER JOIN
✅Steps to follow:
🟣Creating office locations table with fields ID, and address.
Create table
Output:
Output — Office locations
🟣Adding a column by using Alter command.
Alter table syntax
Output:
Output — Alter Table
🟣Now inserting values (random data).
Insert Into command
Output:
Output — Insert Into command
🟣Updating the office id column for the Employee table. ID % 2 is checking what the remainder is if you divide ID by 2. If you divide an even number by 2 it will always have a remainder of 0. Any other number (odd) will result in a non-zero value. Which is what is checking for.
Update syntax
Output:
Output — update command
🟣Need location data for all the Employees — i.e. office address of all the Employees. Creating Office locations Table.
🟣LEFT JOIN Syntax:
Left join syntax
👉Now can see the whole table after running the left join syntax.
Left join command — Table
🟣RIGHT JOIN syntax:
Right join syntax
👉Now can see the whole table after running the Right join syntax.
Right join command — Table
🟣CROSS JOIN Syntax:
Cross join syntax
🔺After running the cross join syntax you will see 400 rows in a table. In simple words — the number of rows in the first table is multiplied by the number of rows in the second table.
Output:
🟣SELF JOIN is just like any other INNER or OUTER Join, with the only exception that both the tables i.e. the Joining and Joined table are the same. we have joined the Employee table to itself to find out Employee names that have the same office location and Department
Comments
Post a Comment