Geek asks and answers

SQL query to find a unique identifier, which can be in three different tables

I have three tables that control products, colors and sizes. Products may or may not have colors and sizes. Colors may or may not have dimensions.

product      color                           size
-------      -------                         -------
id           id                              id
unique_id    id_product (FK from product)    id_product (FK from version)
stock        unique_id                       id_version (FK from version)
title        stock                           unique_id
                                             stock

The column unique_idthat is present in all tables is a serial type (auto-increment), and its counter is shared by three tables, basically it works as a global unique identifier between them.

It works fine, but I'm trying to increase query performance when I need to select some fields based on unique_id.

As I don’t know where is located unique_id, which I am looking for, I use UNION, as shown below:

select title, stock
from product 
where unique_id = 10

UNION

select p.title, c.stock
from color c
join product p on c.id_product = p.id
where c.unique_id = 10

UNION

select p.title, s.stock
from size s
join product p on s.id_product = p.id
where s.unique_id = 10;

? !

1

@ErwinBrandstetter @ErikE . :

1) unique_id ,

2) unique_id, , , ,

SELECT 

    p.title,
    ps.stock

FROM (

    select id as id_product, stock
    from product 
    where unique_id = 10

    UNION

    select id_product, stock
    from color
    where unique_id = 10

    UNION

    select id_product, stock
    from size
    where unique_id = 10

) AS ps

JOIN product p ON ps.id_product = p.id;
+5
5

PL/pgSQL

, plpgsql, , :

CREATE OR REPLACE FUNCTION func(int)
  RETURNS TABLE (title text, stock int) LANGUAGE plpgsql AS
$BODY$
BEGIN

RETURN QUERY
SELECT p.title, p.stock
FROM   product p
WHERE  p.unique_id = $1; -- Put the most likely table first.

IF NOT FOUND THEN
    RETURN QUERY
    SELECT p.title, c.stock
    FROM   color c
    JOIN   product p ON c.id_product = p.id
    WHERE  c.unique_id = $1;
END;

IF NOT FOUND THEN
    RETURN QUERY
    SELECT p.title, s.stock
    FROM   size s
    JOIN   product p ON s.id_product = p.id
    WHERE  s.unique_id = $1;
END IF;

END;
$BODY$;

​​ , , OUT.

RETURNS TABLE PostgreSQL 8.4, RETURN QUERY 8.2. .

, unique_id . id , .

, . , 100000000 , 200000000 300000000 - , . , .

-2147483648 +2147483647, bigint, . integer, , . , bigint text.

CTE (!)

- , SQL :

WITH x(uid) AS (SELECT 10) -- provide unique_id here
    , a AS (
    SELECT title, stock
    FROM   x, product 
    WHERE  unique_id = x.uid
    )
    , b AS (
    SELECT p.title, c.stock
    FROM   x, color c
    JOIN   product p ON c.id_product = p.id
    WHERE  NOT EXISTS (SELECT 1 FROM a)
    AND    c.unique_id = x.uid
    )
    , c AS (
    SELECT p.title, s.stock
    FROM   x, size s
    JOIN   product p ON s.id_product = p.id
    WHERE  NOT EXISTS (SELECT 1 FROM b)
    AND    s.unique_id = x.uid
    )
SELECT * FROM a
UNION ALL
SELECT * FROM b
UNION ALL
SELECT * FROM c;

, , . . PostgreSQL 8.4.

!

, OP PostgreSQL 8.1.
.

PostgreSQL 8.1

, plpgsql , , . EXPLAIN ANALYZE - 8.1.

SELECT title, stock
FROM   product 
WHERE  unique_id = 10

UNION ALL
SELECT p.title, ps.stock
FROM   product p
JOIN  (
    SELECT id_product, stock
    FROM   color
    WHERE  unique_id = 10

    UNION ALL
    SELECT id_product, stock
    FROM   size
    WHERE  unique_id = 10
    ) ps ON ps.id_product = p.id;
+5

, .

, - , ( SerialNumberItems), , .

:

" " " ". , .

Supertype/

/.

, , (, ). , . ( , ), TypeID, FK ParentID, TypeID. , .

, , , . . , , ​​ Title Stock, .

, , , , , + .

, .

-, "has-a" :

  • ( )
  • Product- >
  • Product- >
  • Product- > Color >

. , , ( ), ! . , - . , , -. , ColorAndSize.

, , uniqueid ""? , ? , ( ) - "", " ". .

: Product. Size . Color , . OrderableProduct, ProductId, ColorID, SizeID uniqueid ( -). , , .

, X Y ; , . . , .

- , , , , ProductType ProductTypeOrderables, OrderableProduct - , , . . ProductTypeAllowedColor ProductTypeAllowedSize. , , XXXS, XXS, XS, S, M, L, XL, XXL, XXXL XXXXL, . 6 8 , 24- 8 , 2 , ( ).

, . .

:

PostgreSQL, , unique_id, ?

, , ! , . , , .

+3

auto_increment. , :

:

 C0000001
 C0000002
 C0000003

:

 S0000001
 S0000002
 S0000003
 ...

:

 P0000001
 P0000002
 P0000003
 ...

:

  • , . .
  • . , . , auto_increment .
  • , , , . , , .

:

  • . , 1,2,3 C, S, P.
+1

, unique_id, .

UNION UNION ALL, .

+1

. , {product, color, zsize}. (UNION , , , {product_id, stock} . . . ( !!)

SELECT p.title
        , COALESCE (p2.stock, c.stock, s.stock) AS stock
FROM product p
LEFT JOIN product p2 on p2.id = p.id AND p2.unique_id = 10
LEFT JOIN color c on c.id_product = p.id AND c.unique_id = 10
LEFT JOIN zsize s on s.id_product = p.id AND s.unique_id = 10
WHERE COALESCE (p2.stock, c.stock, s.stock) IS NOT NULL
        ;
+1

More articles:


All Articles