Basic HTML Version

|  | Table of Contents |  | View Full Version

Page 59 - Modular_V2.0_SQL_Flipbook
P. 59

    Output:
                                              +------------+--------------+-----------+
                                              | Staff_name | Learner_name | Location  |
                                              +------------+--------------+-----------+
                                              | Rohit      | Aarav        | Mumbai    |
                                              +------------+--------------+-----------+
                                              | Amisha     | Meera        | Delhi     |
                                              +------------+--------------+-----------+
                                              | Neha       | NULL         | Bangalore |
                                              +------------+--------------+-----------+
                     2.  Right Outer Join (RIGHT JOIN): It shows all rows from the right                  RIGHT JOIN
                         table and the matching rows from the left table. If there is no match,
                         it shows NULL for the left table’s columns.
                                                                                                      Table 1       Table 2
                         Syntax:

                         SELECT table1.column1, table2.column2
                         FROM table1
                         RIGHT JOIN table2

                         ON table1.common_column = table2.common_column;

                         Example using tables: STAFF and LEARNERS
                         SELECT STAFF.Staff_name, LEARNERS.Learner_name, STAFF.Location

                         FROM STAFF
                         RIGHT JOIN LEARNERS

                         ON STAFF.Staff_id = LEARNERS.Learner_id;
                                                                                       Hands On
                         Output:
                     +------------+--------------+----------+
                     | Staff_name | Learner_name | Location |                           Write a query to retrieve the
                     +------------+--------------+----------+
                     | Rohit      | Aarav        | Mumbai   |                           names of customers and the
                     +------------+--------------+----------+                           products they ordered  from
                     | Amisha     | Meera        | Delhi    |
                     +------------+--------------+----------+                           the Customers and Orders
                     | NULL       | Rahul        | NULL     |
                     +------------+--------------+----------+                           tables.

                         VIEWS

                     A view is a virtual table created based on one or more tables. A view has rows and columns like
                     a normal table in a database, but it does not store data itself. Instead, it displays data stored in
                     underlying tables. A view is created using the CREATE VIEW command.

                     Syntax:
                         CREATE VIEW view_name AS

                         SELECT column1, column2
                         FROM table_name
                         WHERE condition;

                                                                                                                         57
                                                                                            Advanced Features of MySQL

| 54 55 56 57 58 59 60 61 62 63 64 |