Hello. I have a question: how to assimilate sql queries, namely a bunch of Join ? All other requests are easy to write, but with Join 'somehow it does not work. Maybe there is where detailed manul, or even video tutorials? I want to have it perfectly, to link tables with at least 7-8 pieces.

    2 answers 2

    Itself faced a similar problem. This moment was hard to understand. It was helped by the visual display placed on different sites on the example of Venn diagrams.

    http://www.k-press.ru/cs/2009/3/join/join.asp - studied on this example.

      If you have 2 tables А and Б , when you join across the field "x", the result will be those records whose "x" matches in both tables. At right/left join all records will be selected, but inconsistencies are filled with null 'mi.

      ps Sure, sql is older than you, during this time how many manuals have been printed and typed lines, google. Even if you read on a wiki :) pss The fact that you have to do join 7-8 tables most likely means that you have incorrectly designed (at least ineffectively) work with the database. Or the database structure is incorrect. Look for "DBMS three normal forms."

      UPDATE : examples:

      inner join:

       create table a as select 1 as x from dual; create table b as select 6 as x from dual; insert into a values (2); insert into a values (3); insert into a values (4); insert into a values (5); insert into b values (7); insert into b select * from a where x > 2; select * from a; X ---------- 1 2 3 4 5 select * from b; X ---------- 3 4 5 6 7 select ax as a, bx as b from a, b where ax = bx; AB ---------- ---------- 3 3 4 4 5 5

      left outer join (we use some data from the previous example, the right one is similar to the left simple strings of which are not filled with zeros from the other side)

       insert into a values (1); insert into a values (2); insert into a values (2); insert into a values (3); insert into a values (3); insert into a values (3); drop table b; create table b as select 1 as x, '1234567890' as count from dual where 1!=1; insert into b values (1, 'one'); insert into b values (2, 'two'); insert into b values (3, 'three'); select x, count (*) as count from a group by x order by x; X COUNT ---------- ---------- 1 2 2 3 3 4 4 1 5 1 select * from b; X COUNT ---------- ---------- 1 one 2 two 3 three select tx as a, t.count, NVL(b.count, 'null') as text from ( select x, count (*) as count from a group by x ) t left outer join b on (bx = tx) order by tx; A COUNT TEXT ---------- ---------- ---------- 1 2 one 2 3 two 3 4 three 4 1 null 5 1 null
      • Search and immediately think about whether you must comply with them: D <br> Especially the third ... - Zowie
      • 3rd most normal))) In general, it makes sense all the same ... - Barton
      • Of course, I would not have to connect 7-8 tables, but why did I say exactly 7-8? just after them connecting 2-4 will seem like a very simple affair) - dogmar
      • This question is also interesting, especially if there is also a group by - Gorets
      • A strange phenomenon turns out, such a query works <code> SELECT * FROM tovar INNER JOIN Manufacturer ON tovar.manufacturer = Manufacturer.id </ code> but there is no such <code> SELECT * FROM tovar FULL OUTER JOIN Manufacturer ON tovar.manufacturer = Manufacturer.id </ code> - dogmar