Views in SQL | Simple View and Complex View Difference

  • Views in SQL:

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.


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

Views in SQL

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


Create view Viewname

as Select column_name1,Coumn_name2 from tablename.


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;



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_no=d.Department_no;

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


About admin 111 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development


  1. Hi, Neat post. There is a problem with your web site in internet explorer, would test this… IE still is the market leader and a large portion of people will miss your wonderful writing due to this problem.

  2. Few queires i have with reference to View,hope you would help me.

    Suppose let’s consider i have created the same view v_employee, now i have inserted few records into view directly via using insert command, now i wanted to delete the records from view but it shouldn’t impact Physical Table, Is there anyway through which we can achieve this.

    • Hi Sajid,
      View is temparary structure not a physical structure.So first thing you cant insert the records in to views.Example.If you have Employee table and you have created the view from employee table.
      create view V_emp as select Eno,name from Employee; then it will create logical structure not physical structure.So if you want to see the records in view you need to insert records in table named Employee.If you want to delete records from view you need to delete it from the table.
      Hope you will get your answer… πŸ™‚

  3. Thank you Admin for your prompt response but I tried to insert the record and it’s working as expected,let’s have a look into the INSERT statement

    insert into v_employee values(5,’ABC’);

    • Dear Sajid,

      You can insert in simple views.

      Cannot insert if the views are created using SQL joining more than one table and also if the SQL uses any in built function.

  4. Thank you for every other informative blog. Where else may just I am getting that kind of information written in such a perfect means? I have a mission that I am simply now operating on, and I have been on the look out for such info.

  5. sir i want pdf for full sql study send me if possible

    and thanx
    i am learning much easiear and more clearly from your website its very helpfull clearing concepts about sql .

  6. Hi Sir,
    I wanted to get Oracle SQL certification.I am trying to learn SQL by myself.sql was never been so easy for me. But after started reading your posts it became very simple.Thanks a lot!! this is superb. πŸ™‚
    If you don’t mind could you please send me the simple SQL notes to my email id. It will be a great help.
    Thanks and regards

17 Trackbacks / Pingbacks

  1. Materialized view in Oracle 11g |Materialized view in Postgres |Materialized view in SQL|View Vs Materialized view
  2. SQL Basic Interview Questions for Freshers | Freshers Interview Questions in SQL
  3. SQL Interview Questions for IBM | Important SQL Interview Questions
  4. SQL Interview Questions for Freshers | Freshers Interview Questions in SQL | SQL Basic interview questions | 20 Most important SQL Questions | SQL Questions with Answers | SQL Questions Asked in Interview
  5. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  6. SQL Interview Questions for Tech Mahindra | Important SQL Interview Questions for Tech Mahindra | Tech Mahindra Interview Questions and answers
  7. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  8. Oracle Partitioning | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  9. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  10. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples
  11. Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example
  12. SQL Functions List,Functions in SQL,SQL Functions example,Aggregate functions,scalar functions,Aggregate functions in sql,scalar functions in sql,functions
  13. Process Commands in Unix | Unix Filter commands | Touch command with example | ps command with examples | Kill command with examples
  14. Unix Directory Commands | Create Directory in Unix | Mkdir Command with Example | PWD command with example | Rmdir command to remove directory | Cd command to change directory
  15. SQL Constraints | Constraints in SQL | Primary Key in SQL | Foreign Key in SQL | Check Constraints in SQL | Null Value
  16. OBIEE
  17. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database

Comments are closed.