Interview Questions And Answers 
-
What types of join algorithms can you have?
8,155 Views |
(100 votes, avg: 4.44)
Loading ...
Answers »
Our Sponsors
Our Sponsors
What types of join algorithms can you have?
8,155 Views | (100 votes, avg: 4.44)
Loading ...
This is the answer for What is a SQL View ?
An output of a query can be stored as a view. View acts like small table which meets our criterion.
View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doens’t physically take any space. Viw is a good way to present data in a particualr format if you use that query quite often.
View can also be used to restrict users from accessing the tables directly.
View is a layer of abstraction on top of one or more tables.
Going through several layered views to access data has a performance price.
venk_man , could you please let me know what an “abstraction” is and explain your answer further about why it leads to an increase in performance price? Thanks a lot.
A view otherwise known as a virtual table is a mere window over the base tables in the database.
This helps us gain a couple of advantages :
1) Inherent security exposing only the data that is needed to be shown to the end user
2) Views are updateable based on certain conditions. For example, updates can only be directed to one underlying table of the view. After modification if the rows or columns don’t comply with the conditions that the view was created with, those rows disappear from the view. You could use the CHECK OPTION with the view definition, to make sure that any updates to make the rows invalid will not be permitted to run.
3) Views are not materialized (given a physical structure) in a database. Each time a view is queried the definition stored in the database is run against the base tables to retrieve the data. One exception to this is to create a clustered index on the view to make it persistant in the database. Once you create a clustered index on the view, you can create any number of non-clustered indexes on the view.
Hope that helps!
At the very first JOIN means catesian product of two tables and join can take some codition to filter out the results.
As example consider two table and a sample query
TableA(id, name)
TableB(id, address)
select * from TableA ta $1 TableB tb on ta.id=tb.id
1. if $1=INNER JOIN
JOIN is equivalent to INNER JOIN
Records will be picked up
if ta.id=tb.id
2. if $1=LEFT OUTER JOIN
Records will be picked up,
if ta.id=tb.id
union
ta.id=null
3. if $1=RIGHT OUTER JOIN
Records will be picked up,
for ta.id=tb.id
union
tb.id=null
4. if $1=NATURAL JOIN
Records will be picked up,
for ta.id=tb.id because their column name is same in both tables.
The NATURAL JOIN keyword specifies that the attributes whose values will be matched between the two tables are those with matching names.
5. CROSS JOIN
Records will be picked up, without any codition,
Means its a INNER JOIN without comparision check.
6. FULL OUTER JOIN
A full outer join combines the results of both left and right outer joins
7. SELF JOIN
An INNER JOIN of own table
6. SELF JOIN
This was the question: What types of join algorithms can you have?
I see answers only for what is a view.
Leave an Answer/Comment