Query Table versus View Table: What is the best practice?

Asked

Viewed 470 times

0

What is best practice for consultations on a banco de dados, perform a direct query on table or in a view of the same? What are the advantages and disadvantages between them?

mysql sql database hsqldb

  • perform a direct query in the table? you mean right in the database? or a query with the table itself? type select * from table;

  • That, I meant if it is better/correct to make a direct query in a table (select * from table) or else create a view with some fields only and do the same query (select * from view). Which of these options would be recommended

1 answer

1


Views are roughly a summary for some query you’ll need to use in different scripts. This facilitates maintenance, for example, when you need to modify something, you would only modify it in the view instead of mecher in all application scripts that use the same query.

If you’ve been floating, let’s take a practical example:

SELECT firula FROM tabela_algo
WHERE foo LIKE '%blah';

This is an SQL query you use in various system scripts

On the page query. you have a query that invokes this query

SELECT x, y, z FROM tabela_qualquer WHERE id = 5 AND firula NOT IN (SELECT firula FROM tabela_algo
    WHERE foo LIKE '%blah')

So far ok. No need to create a view.

But suppose you use the same query on another page, other_page.hph

SELECT x, y, z FROM tabela_diferente WHERE firula IN (SELECT firula FROM tabela_algo
    WHERE foo LIKE '%blah')

Now imagine this in 5, 10, 20 different locations and you need to modify something in the query. You will have to modify on all pages that depend on this query. It is laborious and risks forgetting something, causing system bugs.

In this situation, could simplify creating a VIEW.

With the view, the examples above would look like:

#exemplo 1
SELECT x, y, z FROM tabela_qualquer WHERE id = 5 AND firula NOT IN (SELECT firula FROM tabela_view)

#exemplo 2
SELECT x, y, z FROM tabela_diferente WHERE firula IN (SELECT firula FROM tabela_view)

For a deeper understanding, see this link: What are SQL views? What advantages and disadvantages to using?





The above examples are merely didactic.. dãhnn~~~

Browser other questions tagged mysql sql database hsqldb

You are not signed in. Login or sign up in order to post.