My SQL Database Tutorial: Views and Joins

    optimizdba mysql database tutorial join and view
    Posted November 12, 2018

    Introduction

    In this MySQL database tutorial, we will learn more about Views and how to perform joins efficiently.

    Database View

    Database Views are essentially SQL (Structured Query Language) query with an associated name. Views can frequently be used in the queries by selecting the desired rows or the columns of a table. They can instantly get generated from one or many tables depending upon the mentioned SQL statement resulting in an integration of a table in the form of an already defined SQL query.

    Views let the users perform the following list of tasks efficiently:

    • Management and structuring up the data and information in the way that is incredibly natural and intuitive.
    • Combining and summarizing the data and information from multiple tables to create a structured well-generated
    • Limiting the data access for the users in such a way that they can go through and modify only the information which is
    • Specific columns can get selected by restricting the results using a WHERE clause.

    Database Join

    optimizdba mysql database tutorial optimizdba join

    Database Join is an SQL based operation using which a relationship between the tables is created by combining the table fields having values which are familiar to each other. Join commands are used for the most complex sets of queries in the SQL data management system.

    The following list of points is noticeable while creating the Database Joins:-

    • Join operation is performed in the WHERE clause.
    • The operators such as =, <, >, <>, <=, >=, != along with the specific set of words including BETWEEN, NOT and LIKE are used to join the database tables.
    • The query is written to compare each row of the first table to the rows of the second table for finding the relevant rows for satisfying Join operation.

    Explanation of VIEW with SQL syntax using the relevant example:

    The syntax to create a basic view of the SQL database is as follows:

    CREATE VIEW view_name AS

    SELECT Column 1, Column2….

    FROM table_name

    WHERE [condition];

     

    Multiple tables can get embedded under the SELECT statement similar to the way as in SQL SELECT query.

    Let us consider the following example considering CUSTOMERS table:-

    Sl NoNameAgeAddressSalary
    01John21NY2000
    02Smith22Sydney1000
    03Jordan27Mumbai5000
    04Michael26Hongkong2500

     

    The following SQL query will be written for generating the VIEW from the above-mentioned CUSTOMERS table:-

    CREATE VIEW CUSTOMERS_VIEW AS

    Select Name, Age

    FROM CUSTOMERS;

     

    The users can even query CUSTOMERS_VIEW similar to that of the original table in the following SQL format:

    SELECT * FROM CUSTOMERS_VIEW;

     

    The following result will be generated in relational database format expressing the VIEW:-

    NameAge
    John21
    Smith22
    Jordan27
    Michael26

     

    Explanation of JOIN with SQL syntax using the relevant example:

    The syntax for creating Database Join using SQL statement is as follows:

    SELECT table1.column1, table1.column2, table2.column1 …..

    FROM table1

    JOIN table2

    ON table1.matching_column=table2.matching_column;

     

    INNER JOIN may also be written in place of JOIN to perform the JOIN operation.

    JOIN is actually a clause frequently used in the relational databases for combining the required rows from two or even more tables. The JOIN operation is performed on the basis of columns related between them.

    Let us understand by considering the following example illustrating ‘Orders’ and ‘Customers’ table:-

    Order No.Customer No.Date
    101452005-08-08
    102232005-08-03
    104322005-08-04

     

    Let us consider the following Customers table in association with the above-mentioned Orders table:

    CustomerNumCustNameContactNameCountry
    1AlfredSandersJapan
    2SteveColonelGermany
    3MikaMoroccoGermany

     

    It is noticed that the ‘Customer No.’ column in the orders table directly relates and refers to ‘Customer No.’ in the Customer table. Accordingly, the relationships created between the tables above is the ‘Customer No.’ column.

    The SQL statement mentioned below can perform JOIN operation by selecting the records having matching sets of values in both the tables:

    SELECT Orders.OrderNum, Customers.CustName, Orders.Date

    FROM Orders

    JOIN Customers ON Orders.CustomerNum.=Customers.CustomerNum

    The above-mentioned statement will get executed under the SQL prompt and accordingly, the output relational database will be generated for further execution.

    Conclusion

    In this MySQL Database Tutorial, we have seen how we can perform Joins and fetch views from the databse using SQL.

    These tools allows us to explore the data and help business analysts to take key decisions. This feature makes it very important for database stakeholders such as Expert DBA consultants to know the ins and outs of Join and View.

    If you need auditing or Database performance tuning, get in touch with one of our experts today.


    Tags: , , ,