join in laravel

 

In SQL, the JOIN clause is used to combine rows from two or more tables based on a related column between them. There are several types of joins available in SQL:

1. INNER JOIN:

   • Returns only the rows that have matching values in both tables.
   • Syntax:

     sql
     SELECT columns
     FROM table1
     INNER JOIN table2 ON table1.column_name = table2.column_name;
     

   • Example:

     sql
     SELECT customers.name, orders.order_number
     FROM customers
     INNER JOIN orders ON customers.id = orders.customer_id;
     

2. LEFT JOIN (or LEFT OUTER JOIN):

   • Returns all rows from the left table (table1) and matching rows from the right table (table2).
   • If there are no matching rows in the right table, NULL values are returned.
   • Syntax:

     sql
     SELECT columns
     FROM table1
     LEFT JOIN table2 ON table1.column_name = table2.column_name;
     

   • Example:

     sql
     SELECT customers.name, orders.order_number
     FROM customers
     LEFT JOIN orders ON customers.id = orders.customer_id;
     

3. RIGHT JOIN (or RIGHT OUTER JOIN):

   • Returns all rows from the right table (table2) and matching rows from the left table (table1).
   • If there are no matching rows in the left table, NULL values are returned.
   • Syntax:

     sql
     SELECT columns
     FROM table1
     RIGHT JOIN table2 ON table1.column_name = table2.column_name;
     

   • Example:

     sql
     SELECT customers.name, orders.order_number
     FROM customers
     RIGHT JOIN orders ON customers.id = orders.customer_id;
     

4. FULL JOIN (or FULL OUTER JOIN):

   • Returns all rows when there is a match in either table.
   • If there is no match, NULL values are returned for missing columns.
   • Syntax:

     sql
     SELECT columns
     FROM table1
     FULL JOIN table2 ON table1.column_name = table2.column_name;
     

   • Example:

     sql
     SELECT customers.name, orders.order_number
     FROM customers
     FULL JOIN orders ON customers.id = orders.customer_id;
     

5. CROSS JOIN:

   • Returns the Cartesian product of the two tables (all possible combinations of rows).
   • Syntax:

     sql
     SELECT columns
     FROM table1
     CROSS JOIN table2;
     

   • Example:

     sql
     SELECT customers.name, products.name
     FROM customers
     CROSS JOIN products;
     

These are the basic types of joins in SQL. Each join type serves different purposes and can be used based on the specific requirements of your query.