SQL Tutorial

• What is SQL
• What is RDBMS
• Commonly Used Relational Databases
• SQL Syntax
• SQL Data Types
• SQL CREATE DATABASE
• SQL DROP DATABASE
• SQL USE
• SQL CREATE TABLE
• SQL DROP TABLE
• SQL RENAME TABLE
• SQL INSERT INTO
• SQL SELECT
• SQL WHERE
• SQL AND and OR
• SQL UPDATE
• SQL DELETE
• SQL LIKE
• SQL TOP
• SQL ORDER BY
• SQL GROUP BY
• SQL DISTINCT
• SQL Constraints
• SQL NULL
• SQL NOT NULL
• SQL DEFAULT
• SQL UNIQUE
• SQL Primary Key
• SQL Foreign Key
• SQL CHECK
• SQL INDEX
• SQL JOIN
• SQL INNER JOIN
• SQL LEFT JOIN
• SQL RIGHT JOIN
• SQL FULL JOIN
• SQL SELF JOIN
• SQL CROSS JOIN
• SQL UNION and UNION ALL
• SQL AS
• SQL ALTER TABLE
• SQL TRUNCATE TABLE
• SQL View
• SQL HAVING
• SQL Transaction
• SQL Temporary Tables
• SQL Clone Table
• SQL Sub Query
• SQL AUTO_INCREMENT
• SQL Injection

SQL INNER JOIN

The INNER JOIN keyword in SQL is used to combine rows from two or more tables, based on a related column between them. This keyword selects records that have matching values in both tables.

The basic syntax of INNER JOIN is as follows:

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider the following two tables:

Orders table:

Customers table:

We can use INNER JOIN to combine these tables based on the CustomerID:

SELECT Orders.OrderID, Customers.Name, Orders.OrderAmount
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

The result would be:

This query selects all orders, along with the names of the customers who placed them.

Note:

1. INNER JOIN is the most common type of join, and it is also known simply as JOIN.

2. INNER JOIN selects records that have matching values in both tables. If there is a record in the first table that matches the WHERE clause, but there is no record in the second table that matches the WHERE clause, the INNER JOIN will not select that record.

As always, the exact syntax may vary between different SQL dialects, so be sure to consult the documentation for the SQL dialect you're using.

1. Joining Tables with INNER JOIN:

   • Description: INNER JOIN combines rows from two or more tables based on a related column between them.
   • Code Example:

     SELECT Orders.OrderID, Customers.CustomerName
     FROM Orders
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
     

2. SQL INNER JOIN Example:

   • Description: Retrieves orders along with corresponding customer names using INNER JOIN.
   • Code Example:

     SELECT Orders.OrderID, Customers.CustomerName
     FROM Orders
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
     

3. INNER JOIN vs LEFT JOIN vs RIGHT JOIN:

   • Description: INNER JOIN retrieves only matching rows between tables, while LEFT JOIN and RIGHT JOIN include unmatched rows from the left or right table, respectively.

   • Code Example (LEFT JOIN):

     SELECT Orders.OrderID, Customers.CustomerName
     FROM Orders
     LEFT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
     

   • Code Example (RIGHT JOIN):

     SELECT Orders.OrderID, Customers.CustomerName
     FROM Orders
     RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
     

4. Using WHERE Clause with INNER JOIN:

   • Description: WHERE clause can be used to further filter the result set after the INNER JOIN operation.
   • Code Example:

     SELECT Orders.OrderID, Customers.CustomerName
     FROM Orders
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
     WHERE Orders.OrderDate > '2022-01-01';
     

5. Handling NULL Values in INNER JOIN:

   • Description: NULL values may result from an INNER JOIN when there is no match. Use additional conditions or handle NULLs as needed.
   • Code Example (Handling NULLs with COALESCE):

     SELECT Orders.OrderID, COALESCE(Customers.CustomerName, 'Unknown') AS CustomerName
     FROM Orders
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;
     

6. Multiple Conditions in INNER JOIN Statement:

   • Description: INNER JOIN can involve multiple conditions for matching rows between tables.
   • Code Example:

     SELECT Orders.OrderID, Customers.CustomerName
     FROM Orders
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID AND Orders.Shipped = 1;
     

7. INNER JOIN with Multiple Tables in SQL:

   • Description: INNER JOIN can be extended to involve more than two tables for complex queries.
   • Code Example:

     SELECT Orders.OrderID, Customers.CustomerName, Products.ProductName
     FROM Orders
     INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
     INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
     INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID;