SQL JOINS | LEARN SQL

HEY GIRUILLIANS WELCOME BACK TO OUR SQL SERIES WE HAVE POSTED 4 BLOGS ABOUT AND THIS IS 6TH IF YOU ARE NEW PLEASE READ PREVIOUS BLOGS TO LEARN WITH FLOW LET’S START

IN THE PREVIOUS CLASS WE HAD LEARNT ABOUT

A. SQL ORDER BY FUNCTIONS:

• ORDER BY CLAUSE

• ORDER BY ASC

• ORDER BY DESC

• ORDER BY RANDOM

• ORDER BY MULTIPLE COLUMNS

TODAY WE WILL LEARN ABOUT

A. SQL JOINS

• SQL Outer Join

• LEFT OUTER JOIN (or LEFT JOIN)

• RIGHT OUTER JOIN (or RIGHT JOIN)

• FULL OUTER JOIN

• LEFT JOIN (IF NULL)

• SQL Cross Join

• SQL KEYS

• PRIMARY KEY

• FOREIGN KEY

• COMPOSITE KEY

• UNIQUE KEY

• ALTERNATE KEY

TABLES IN USE

TABLE 1 : GUERILLATECK

TABLE 2: GUERILLA CARS

1. SQL JOINS

In SQL, joins are used to combine rows from two or more tables based on related columns. Joins allow you to retrieve data from multiple tables in a single query, enabling you to establish relationships and extract meaningful information from your database.

2. SQL OUTER JOIN

In SQL, an OUTER JOIN is a type of join that includes unmatched rows from one or both tables involved in the join. It allows you to retrieve records even when there is no match between the joining columns in the tables.

There are three types of OUTER JOIN in SQL:

• LEFT OUTER JOIN (or LEFT JOIN):

Returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table columns.  

 

SYNTAX IS AS FOLLOWS:-

SELECT column1, column2, ...

FROM table1

LEFT JOIN table2 ON table1.column = table2.column;

STRUCTURE FOR LEFT OUTER JOIN

FOR EXAMPLE

OUTPUT:

• RIGHT OUTER JOIN:-

Returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table columns.

 

SYNTAX IS AS FOLLOWS:-

SELECT column1, column2, ...

FROM table1

RIGHT JOIN table2 ON table1.column = table2.column;

STRUCTURE FOR RIGHT OUTER JOIN

For example

Output

NOTE*- Outer joins are useful when you want to include all records from one table, regardless of whether there is a match in the other table. They are typically used when you need to join tables based on non-matching or optional relationships. By including unmatched rows, you can still retrieve relevant data and perform analysis or reporting.

• LEFT JOIN (IF NULL)

In SQL, you can use a LEFT JOIN and the IS NULL condition to retrieve records from the left table that do not have a matching record in the right table. This allows you to handle situations where you want to perform specific actions or display certain values when there is no match in the joined table.

 

SYNTAX IS AS FOLLOWS:-

SELECT column1, column2, ...

FROM table1

LEFT JOIN table2 ON table1.column = table2.column

WHERE table2.column IS NULL;

Structure of LEFT JOIN (IF NULL)

For example

Output

There are no null values so no entries are displayed

• RIGHT JOIN (IF NULL)

In SQL, you can use a RIGHT JOIN and the IS NULL condition to retrieve records from the right table that do not have a matching record in the left table. This allows you to handle situations where you want to perform specific actions or display certain values when there is no match in the joined table.

The syntax is as follows:-

SELECT column1, column2, ...

FROM table1

RIGHT JOIN table2 ON table1.column = table2.column

WHERE table1.column IS NULL;

Structure of Right JOIN (IF NULL)

For example:

Output

There are no null values so no entries are displayed

• SQL CROSS JOIN 

In SQL, a CROSS JOIN, also known as a Cartesian join, is used to combine every row from the first table with every row from the second table. It does not rely on any matching condition between the tables.

 

The syntax for a CROSS JOIN is as follows:

SELECT column1, column2, ...

FROM table1

CROSS JOIN table2;

THE STRUCTURE IS AS FOLLOWS

Output:-

• SQL KEYS:

In SQL, keys are used to identify and establish relationships between tables in a database. They help ensure data integrity, enforce uniqueness, and facilitate efficient data retrieval. There are several types of keys commonly used in SQL:

a. SQL PRIMARY KEYS

A primary key is a unique identifier for each record in a table. It ensures that each row in the table is uniquely identified and allows for quick and efficient retrieval of specific records. A primary key cannot contain duplicate or NULL values.

b. SQL FOREIGN KEY

A foreign key establishes a relationship between two tables by referring to the primary key of another table. It ensures referential integrity, meaning that the values in the foreign key column must match the values in the primary key column of the referenced table or be NULL. Foreign keys are used to enforce data integrity and maintain relationships between related tables.

 

c. SQL FOREIGN KEY  

A foreign key establishes a relationship between two tables by referring to the primary key of another table. It ensures referential integrity, meaning that the values in the foreign key column must match the values in the primary key column of the referenced table or be NULL. Foreign keys are used to enforce data integrity and maintain relationships between related tables.

 

d. SQL UNIQUE KEY 

  

A unique key enforces uniqueness on one or more columns in a table, similar to a primary key. However, unlike a primary key, a unique key can allow NULL values. Each unique key constraint ensures that the values in the specified column(s) are unique across the table.

e. SQL ALTERNATE KEY

In SQL, an alternate key refers to a candidate key that is not selected as the primary key for a table. It is a unique identifier for each record, just like a primary key, but it is not designated as the primary means of identification. 

  

While a primary key is chosen as the primary identifier for a table, an alternate key provides an additional unique identifier. This can be useful in scenarios where multiple unique identifiers are required or when there are multiple columns or combinations of columns that can uniquely identify a record.