Will the key in sql still remain the key in the view

Question

Say I have a mysql table named FISH with fields A, B and C.

I ran SELECT * FROM FISH . This gives me an idea with all the fields. So, if A was the key in the source table, is that also the key in the view? Value if i have table FISH2 and i ran

  SELECT * FROM (SELECT * FROM FISH) D, (SELECT * FROM FISH2) E WHERE DA = EA 

Will the corresponding fields still be displayed?

Now let's take this step even further. If I run

 SELECT * FROM (SELECT CONCAT(A,B) AS DUCK, C FROM FISH) D, (SELECT CONCAT(A,B) AS DUCK2, C FROM FISH2) E WHERE D.DUCK = E.DUCK2 

If A and B were keys in the source tables, would their concatenation also be key?

Thanks:)

Answer 1

If A is the key in fish , any projection only onto the fish will create a result set where A is still unique.

A join between tabular fish and any table with a 1: 1 ratio (for example, fish_type) will create a result set where A is unique.

Joining another table that has a 1: M or M: M relationship from fish (for example, fish_beits) will NOT produce a result where A is unique unless you provide a filter predicate on the "other" side (for example, bait='Dynamite' ).

 SELECT * FROM (SELECT * FROM FISH) D, (SELECT * FROM FISH2) E WHERE DA = EA 

... is logically equivalent to the following statement, and most databases (including MySQL) will perform the conversion:

 select * from fish join fish2 on(fish.a = fish2.a) 

Whether A is unique in the result set depends on the key fish2 and their relationship (see above).

Concatenation does not preserve uniqueness. Consider the following case:

 concat("10", "10") => "1010" concat("101", "0") => "1010" 

Therefore, your final request ...

 SELECT * FROM (SELECT CONCAT(A,B) AS DUCK, C FROM FISH) D ,(SELECT CONCAT(A,B) AS DUCK2, C FROM FISH2) E WHERE D.DUCK = E.DUCK2 

... will not (necessarily) produce the same result as

 select * from fish join fish2 on( fish.a = fish2.a and fish.b = fish2.b ) 

I wrote necessarily, because collisions depend on actual values. Some time ago I was looking for a mistake when the root cause was just that. The code worked several years before the error showed up.

Answer 2

If “key” means “unique,” ​​yes, the tuples of the Cartesian product by unique values ​​will be unique. (This can be proved by reductio ad absurdum.)

Answer 3

For step 1, think of a view as a subquery containing everything in an AS clause when CREATE VIEW was run.

For example, if view v is created as SELECT a, b, c FROM t , then when executed ...

 SELECT * FROM v WHERE a = some_value 

... it is conceptually regarded as ...

 SELECT * FROM (SELECT a, b, c FROM t) WHERE a = some_value 

Any database with a decent optimizer will notice that column a is passed directly to the results and that it can take advantage of indexing in t (if any) by moving it to a subquery:

 SELECT * FROM (SELECT a, b, c FROM t WHERE a = some_value) 

All this happens behind the scenes and is not an optimization that you need to do yourself. Obviously, this cannot be done for every condition in the WHERE , but understanding where you can is part of writing a good optimizer.

For step 2, the concatenated keys will be part of the intermediate results, and regardless of whether the database decides whether they need indexing, is an implementation detail. Also check out fche's comment on duplication.

If your database contains an explanation of the query plan, running it and learning how to interpret the results will give you a lot of information about what makes your queries work fast and what slows them down.