SQL Difference Between SQL Tutorials

What is Difference between Simple view and complex view ?

Amit S February 4, 2017

Views in SQL is nothing but the logical table created from one or more tables. We can use the views to fetch the columns from one or more different tables at a time. In real life specifically views are used in Reporting purpose. To create a report we need data from different tables and need to show it on a single dashboard so we are using the views to fetch the data from different tables. View can contain all rows from the table or selected rows from the table. I would like to give more information about difference between simple view and complex view in detail.

CLICK HERE TO GET INFORMATION ON MATERIALIZED VIEW……..

View is nothing but virtual table in sql which will fetch data from one or more physical tables

There are 2 types of views in SQL:

1.Simple View-Simple view is view created on single table

2.Complex View-Complex view is view created on more than 1 tables

  • Simple View:

When user wants data or some columns from same table then simple view is used. For an example if there is employee table which has Employee_num,Employee_name,salary columnns and we just need to see Employee_num,Employee_name then user can create a simple view.

Employee_num Employee_name Department Salary
1 Amit OBIEE 680000
2 Rohan OBIEE 550000
3 Rohit OBIEE 430000

Difference between simple view and complex view

We want to restrict data from the above table and show only Employee_Num and Employee_name then we will use the view.

Syntax:

Create view Viewname

as Select column_name1,Coumn_name2 from tablename.

Example:

Create view V_Employee

as Select Employee_num,Employee_name from Employee;

You can access the view using Select statement of SQL:

select * from V_Employee;

Output:

Employee_num Employee_name
1 Amit
2 Rohan
3 Rohit
  • Complex View:

Complex view is created on using more than one tables. When user wants to retrieve data from more than 1 table then we have to use complex views. To create complex view there should be relation between 2 tables else cartesian product will come by joining 2 tables. There should be some joining conditions, some filters needs to be considered while creating complex views.

Suppose there are 2 tables:

1.Employee tables:-Employee_name,Employee_num,Department_code columns

2.Department:-Department_code,Deptartment_name columns

We need to create view where we want to show the associated department to employee.Here We need to use complex join.

Create view V_EMP_DEPT

as Select e.Employee_name,d.Department_name

from Employee e,Department d

where e.Department_code=d.Department_code;

If you have any issues regarding SQL Views or If you want notes in PDF format comment below section.

Difference between simple view and complex view in tabular format :

Simple View Complex View
Simple view is the view which has been created by only one table. Complex view is the view where we require to join multiple tables together for specified result.
We can not use group functions in simple views We can able to use group functions
You can perform multiple DML operations using simple view You can not perform DML operations using complex view
It does contain data from only one table It does contain data from multiple tables
We can not be able to use group by clause in simple view as grouping is not allowed We can use group by clause in complex view
We can not be able to use pseudo columns like rownum and distinct keyword We can use the pseudo columns and rownum
Example :

Create view V_simple_view

As select Empno,Empname from Employee;

 Example :

Create view V_Complex_view

As Select e.emp_no,d.dept_no,e.emp_name from Employee e ,Department d where e.dept_no=e.dept_no;

I hope you are clear with difference between simple view and complex view with examples. If you like this article or if you have any issues with the same kindly comment in comments section.

CLICK HERE TO GET  20 MOST IMPORTANT SQL INTERVIEW QUESTIONS
HOME

60 Replies to “What is Difference between Simple view and complex view ?”

  4. hi Amit,

    can u please share me the complete oracle notes with simple understanding and example, not as complex as other tutorials

          1. please sir send me the complete oracle notes with simple understanding and example, and also interview questions

  7. Hi Amit,

    Could you send all topic in PDF format to my email id. Thanks in Advance!!

  8. can u please share me the complete SQL,Unix notes with simple understanding and example, not as complex as other tutorials

  9. Hi Amit,

    Thanks! It is easy to understand.
    Please share sql documents and interview questions for experienced professional.

    —
    Regards
    SKG

  10. Department code is the common column in both the tabels.
    Then how can you write
    e.department_no =d.department_no
    As department_no is not a column in any of the tables. I

    1. Hello A S,

      Thanks for catching up the typo error.I have changed the typographic error and corrected it!!

      Regards and thanks,
      Amit S

  15. Hi Amit,
    This is soumya ,could you please share me the complex SQL queries with its explanations and perfermance tuning notes with simple understanding and examples

  20. Hi,
    Could you pls send me advanced sql and performance tuning notes.
    Thanks in advance.

  22. Hi Amit,
    Can u tell me some real time examples for creating a view(say for example motor insurance domain)..and some performance tuning methods..

    Thanks in advance

  27. hi amit
    can u please share me the details of creating indexes, Views, complex Stored Procedures, user defined functions, derived tables, Temporary Tables, Sub query, CTE and Triggers.

  30. Hi Amit, the content you explained is so easy to understand. You made that look easy. Thanks for that. Can you please send the pdf formats for VIEWS and STORED PROCEDURES (IF POSSIBLE).

    Thank you

  32. Good explanation!
    I have interview coming up for a BI developer, can you share anything that can help?
    Thanks

  33. please sir send me the complete oracle notes with simple understanding and example, and also interview questions

  34. Hi Amit,

    can u please share me the complete oracle notes with simple understanding and example, not as complex as other tutorials, please also share the contect for view and materialized , Non-materialized view.
    Email : rv50611@gmail.com

    Thanks,
    Rahul

  35. Hi Amit, Can you please share me Materialized view documents where it is used real time .

Comments are closed.