**# JOINS FUNCTIONS

Example INNER JOIN:**

SELECT
      l.* -- an alias from loan table and selecting all
FROM bank.loan AS l -- creating a alias from the loan table 'AS l'
INNER JOIN bank.account AS a ON a.account_id = l.account_id;
    -- JOIN/INNER JOIN combine records from two tables whenever there are matching 
    -- values in a field common to both tables.
    -- both values need to be the same.

**Example LEFT JOIN:**

SELECT
      *
FROM bank.account AS a -- creating an alias from the account table 'AS a'
LEFT JOIN bank.loan AS l ON a.account_id = l.account_id
         -- LEFT JOIN select all rows from the left table 
         -- and also the matched values between the two tables.
WHERE district_id = 2;

**Example RIGHT JOIN:**

SELECT
      *
from bank.account AS a -- creating an alias from the account table 'AS a'
RIGHT JOIN bank.loan AS l ON a.account_id = l.account_id
          -- RIGHT JOIN returns all rows from the right table
          -- and the matching records from the left table.
WHERE district_id = 2;

**Example MULTIPLE JOIN:**

SELECT
	 *
FROM bank.disp d
JOIN bank.client c ON c.client_id = d.client_id
JOIN bank.card ca ON d.disp_id = ca.disp_id AND ca.type = 'gold';
     -- Here we are using a Multiple Join and replacing the 'WHERE' clause with 'AND'
     -- We can always use 'AND' as 'WHERE' in the same JOIN line

**Example SELF JOIN:**

SELECT
     *
FROM bank.account a1
JOIN bank.account a2 ON a1.account_id <> a2.account_id
     -- self join allows you to join a table to itself. It helps query hierarchical data 
     -- or compare rows within the same table.
     -- self join uses the inner join or left join clause.
AND a1.district_id = a2.district_id
ORDER BY a1.district_id;

**Example CROSS JOIN:**

SELECT
     *
FROM (
      SELECT DISTINCT TYPE FROM bank.card) sub1
CROSS JOIN (
            SELECT DISTINCT TYPE FROM bank.disp) sub2;
        -- cross join is a type of join that returns the Cartesian product of rows 
        -- from the tables in the join.
        -- it combines each row from the first table with each row from the second table.