Opa, e ai, blz?<\/p>\n
A alguns dias estava conversando com um Brother que \u00e9 programador, falando sobre c\u00f3digos disse em certa hora que era s\u00f3 ele usar um Inner Join, que a query ficaria simples.<\/p>\n
O cara vira e me diz “bicho, seu eu soubesse usar isso, sei que ficaria mais simples, mas, agora, vai ser tudo com and mesmo…”<\/p>\n
Como achei tosco demais e estava com vontade de escrever pelo menos um b\u00e1sico sobre o assunto, enviei para ele o texto abaixo, exatamente como est\u00e1…<\/p>\n
———————————-8<———————————-<\/p>\n
Segue uma amostra dos Inner join!!!! \ud83d\ude42<\/p>\n
Veja uma tabela que tenho de Planos, que cont\u00e9m um c\u00f3digo e uma descri\u00e7\u00e3o:<\/p>\n
mysql>\r\nselect vc_plano, vc_descricao\r\nfrom table_planos\r\nwhere vc_plano=34;\r\n+----------+---------------+\r\n| vc_plano | vc_descricao\u00a0 |\r\n+----------+---------------+\r\n| 34\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 |\r\n+----------+---------------+\r\n1 row in set (0.00 sec)<\/pre>\nAgora, vou buscar na minha tabela de Clientes, um site com o plano 34:<\/p>\n
mysql>\r\nselect vc_domain, vc_plano\r\nfrom table_clientes\r\nwhere vc_domain like 'site.com.br';\r\n+-------------+----------+\r\n| vc_domain\u00a0\u00a0 | vc_plano |\r\n+-------------+----------+\r\n| site.com.br | 34\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-------------+----------+\r\n1 row in set (0.00 sec)<\/pre>\nOk, agora, quero buscar nas 2 alguns dados, o site e a descri\u00e7\u00e3o do plano dele:<\/p>\n
mysql>\r\nselect a.vc_domain, b.vc_descricao\r\nfrom table_clientes a\r\ninner join table_planos b on (a.vc_plano = b.vc_plano)\r\nwhere a.vc_domain like 'site.com.br';\r\n+-------------+---------------+\r\n| vc_domain\u00a0\u00a0 | vc_descricao\u00a0 |\r\n+-------------+---------------+\r\n| site.com.br | Plano 0000034 |\r\n+-------------+---------------+\r\n1 row in set (0.00 sec)<\/pre>\nVeja que retornei o site da tabela Clientes e a descri\u00e7\u00e3o da tabela Planos.
\nEsse seria o metodo mais simples de se escrever um inner join, escrevendo mesmo INNER JOIN….
\nTamb\u00e9m \u00e9 poss\u00edvel usando outra sintaxe, mas, essa \u00e9 a que eu uso \ud83d\ude42<\/p>\nVeja que comparo o mesmo campo (n\u00e3o precisariam ter o mesmo nome, mas ajuda) nas 2 tabelas.<\/p>\n
A sintaxe \u00e9 normalmente essa:
\nSELECT a.*, b.* FROM table a
\nINNER JOIN table b ON (a.campo = b.campo)<\/p>\nVeja a mesma busca, sem o where:<\/p>\n
mysql>\r\nselect a.vc_domain, b.vc_descricao\r\nfrom table_clientes a\r\ninner join table_planos b on (a.vc_plano = b.vc_plano) where a.vc_plano=34;\r\n+------------------------------+---------------+\r\n| vc_domain\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | vc_descricao\u00a0 |\r\n+------------------------------+---------------+\r\n| site.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 |\r\n| web-site2.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 |\r\n+------------------------------+---------------+\r\n2 rows in set (0.00 sec)<\/pre>\nLegal, acho que agora, vc j\u00e1 sabe tudo de inner join \ud83d\ude42<\/p>\n
Vamos para mais um exemplo, agora, fazendo 2 inner joins.<\/p>\n
Primeiro, vamos ver a tabelam STATUS:<\/p>\n
mysql> select * from table_status;\r\n+-----------+-----------+\r\n| dc_status | vc_status |\r\n+-----------+-----------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 | Ativo\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2 | Suspenso\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 3 | Cancelado |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 4 | Novo\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+-----------+-----------+\r\n4 rows in set (0.00 sec)<\/pre>\nE, vamos revelar mais um campo da tabela clientes:<\/p>\n
mysql>\r\nselect vc_domain, vc_plano, dc_status\r\nfrom table_clientes\r\nwhere vc_domain like 'site.com.br';\r\n+-------------+----------+-----------+\r\n| vc_domain\u00a0\u00a0 | vc_plano | dc_status |\r\n+-------------+----------+-----------+\r\n| site.com.br | 34\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n+-------------+----------+-----------+\r\n1 row in set (0.00 sec)<\/pre>\nAgora, vamos trazer a descri\u00e7\u00e3o dos status a outra tabela:<\/p>\n
mysql>\r\nselect a.vc_domain, b.vc_descricao, c.vc_status\r\nfrom table_clientes a\r\ninner join table_planos b on (a.vc_plano = b.vc_plano)\r\ninner join table_status c on (a.dc_status = c.dc_status)\r\nwhere a.vc_plano=34;\r\n+------------------------------+---------------+-----------+\r\n| vc_domain\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | vc_descricao\u00a0 | vc_status |\r\n+------------------------------+---------------+-----------+\r\n| site.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 | Ativo\u00a0\u00a0\u00a0\u00a0 |\r\n| web-site2.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 | Cancelado |\r\n+------------------------------+---------------+-----------+\r\n2 rows in set (0.00 sec)<\/pre>\nDesse jeito, voce ganha muito nos sistemas, pois, n\u00e3o precisaria comparar strings,
\nvoc\u00ea pode comparar os c\u00f3digos e sempre que quiser mudar os valores de strings, altera
\nsomente em uma tabela.<\/p>\nOk, voc\u00ea j\u00e1 \u00e9 um especialista.. Sabe tudo de inner join… mas…. seu DBA \u00e9 um merda
\ne est\u00e1 cagando para a integridade do banco de dados..<\/p>\nVeja o que ele fez:<\/p>\n
mysql> update table_clientes set\u00a0 dc_status=null where vc_domain='web-site2.com.br';\r\nQuery OK, 1 row affected, 1 warning (0.07 sec)\r\nRows matched: 1\u00a0 Changed: 1\u00a0 Warnings: 1<\/pre>\nE veja o que ocorreu com a \u00faltima query que voc\u00ea havia feito:<\/p>\n
mysql>\r\nselect a.vc_domain, b.vc_descricao, c.vc_status\r\nfrom table_clientes a\r\ninner join table_planos b on (a.vc_plano = b.vc_plano)\r\ninner join table_status c on (a.dc_status = c.dc_status)\r\nwhere a.vc_plano=34;\r\n+------------------------------+---------------+-----------+\r\n| vc_domain\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | vc_descricao\u00a0 | vc_status |\r\n+------------------------------+---------------+-----------+\r\n| site.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 | Ativo\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------------------+---------------+-----------+\r\n1 rows in set (0.00 sec)<\/pre>\nCADE O web-site2.com.br !?!?!?!?!?!?!<\/p>\n
Sei l\u00e1.. PERGUNTA PRO DBA!!! \ud83d\ude42<\/p>\n
Inner join, somente faz o que voc\u00ea manda.. igual um “and”.. ele sabe se \u00e9
\npara trazer um cara na compara\u00e7\u00e3o. Ou n\u00e3o.<\/p>\nComo agora o valor da coluna STATUS desse registro da tabela CLIENTE,
\nn\u00e3o \u00e9 mais igual a nenhum valor da tabela STATUS, a compara\u00e7\u00e3o a = c n\u00e3o bate,
\nlogo, ele n\u00e3o ser\u00e1 retornado em suas buscas.<\/p>\nMas, nada tema.. para esses casos existe uma solu\u00e7\u00e3o.<\/p>\n
Caso voc\u00ea queira trazer todos os valores, independente dos STATUS serem
\niguais, voc\u00ea pode usar o irm\u00e3o do inner join, o LEFT JOIN, veja:<\/p>\nmysql>\r\nselect a.vc_domain, b.vc_descricao, c.vc_status\r\nfrom table_clientes a\r\ninner join table_planos b on (a.vc_plano = b.vc_plano)\r\nLEFT JOIN table_status c on (a.dc_status = c.dc_status)\r\nwhere a.vc_plano=34;\r\n+------------------------------+---------------+-----------+\r\n| vc_domain\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | vc_descricao\u00a0 | vc_status |\r\n+------------------------------+---------------+-----------+\r\n| site.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 | Ativo\u00a0\u00a0\u00a0\u00a0 |\r\n| web-site2.com.br\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Plano 0000034 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------------------------------+---------------+-----------+\r\n2 rows in set (0.00 sec)<\/pre>\nEle segue as mesmas regras, a diferen\u00e7a \u00e9 que ele retorna todos os resultados
\nque voc\u00ea pediu (where a.vc_plano=34), mesmo que a regra de compara\u00e7\u00e3o dele, n\u00e3o
\nexista.<\/p>\nCara<\/p>\n
Esse \u00e9 um basi\u00e7\u00e3o sobre isso, voc\u00ea sempre pode melhorar, procurar por exemplo
\no right join… union e criar horrendas sub querys, o importante \u00e9 funcionar! \ud83d\ude42<\/p>\nPara finalizar, veja essa bizarrice:<\/p>\n
\r\n$sqlteste2 = "select distinct a.products_id from products_to_categories a\r\ninner join products_description b on (a.products_id = b.products_id)\r\ninner join products_to_categories c on (a.categories_id=c.categories_id)\r\nwhere a.categories_id in\r\n(select categories_id from categories where parent_id in\r\n(select categories_id from categories_description where uf='$tbestados')\r\nor categories_id = (select categories_id from categories_description where uf='$tbestados'))\r\nand a.products_id in\r\n(select products_id from products_to_categories where categories_id='$categoria')\r\n\r\norder by b.products_name";\r\n<\/pre>\nIsso somente significa… que as coisas podem ser pioradas ao extremo!!! \ud83d\ude42<\/p>\n
[]’s<\/p>\n","protected":false},"excerpt":{"rendered":"
Pequeno guia pr\u00e1tico que enviei para um amigo sobre como usar inner joins para facilitar a vida!<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[16],"tags":[6],"class_list":["post-42","post","type-post","status-publish","format-standard","hentry","category-sql","tag-sql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/posts\/42","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/comments?post=42"}],"version-history":[{"count":4,"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/posts\/42\/revisions"}],"predecessor-version":[{"id":46,"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/posts\/42\/revisions\/46"}],"wp:attachment":[{"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/media?parent=42"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/categories?post=42"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tia-go.net\/wordpress\/wp-json\/wp\/v2\/tags?post=42"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}