web analytics

SQL Joins..

Assume we have the following two tables.
 Table A is on the left, and Table B is on the right.
 We'll populate them with four records each.
A             B
id name       id  name
-- ----       --  ----
1  Pirate     1   Rutabaga
2  Monkey     2   Pirate
3  Ninja      3   Darth Vader
4  Spaghetti  4   Ninja
Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.  
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id  name       id   name
--  ----       --   ----
1   Pirate     2    Pirate
3   Ninja      4    Ninja
Inner join produces only the set of records that match in both Table A and Table B.    
Venn diagram of SQL inner join
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id    name       id    name
--    ----       --    ----
1     Pirate     2     Pirate
2     Monkey     null  null
3     Ninja      4     Ninja
4     Spaghetti  null  null
null  null       1     Rutabaga       
null  null       3     Darth Vader
Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.
Venn diagram of SQL cartesian join
 
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id  name       id    name
--  ----       --    ----
1   Pirate     2     Pirate
2   Monkey     null  null
3   Ninja      4     Ninja
4   Spaghetti  null  null
Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.  
Venn diagram of SQL left join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id  name       id     name
--  ----       --     ----
2   Monkey     null   null
4   Spaghetti  null   null
To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, thenexclude the records we don't want from the right side via a where clause.
join-left-outer.png
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null 
OR TableB.id IS null

id    name       id    name
--    ----       --    ----
2     Monkey     null  null
4     Spaghetti  null  null
null  null       1     Rutabaga
null  null       3     Darth Vader
To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.
join-outer.png
There's also a Cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:
SELECT * FROM TableA
CROSS JOIN TableB
This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.   Many thanks to the original source of this..  and I hope it helps others..

PMR446 frequencies

There is a total of eight PMR466 channels or PMR466 frequencies that can be used. These are spaced 12.5kHz apart from each other. As the name of the system suggests, the PMR446 frequencies are in the UHF portion of the radio spectrum and located around 446 MHz. The PMR446 frequencies have been harmonised (but not necessarily authorised) for use across Europe. The channel centre frequencies are as follows:
PMR466 Channel Frequency (MHz)
1 446.00625
2 446.01875
3 446.03125
4 446.04375
5 446.05625
6 446.06875
7 446.08125
8 446.09375

A FEW UNIQUE PLUS TRADITIONAL USES FOR COLLOIDAL Silver

When you control a source of penny's-per-gallon make-it-yourself high concentration silver colloid you can use it for hundreds of health improvement applications. A few are suggested here. Add to suspected drinking water when traveling or camping. Silver-sprayed burns heal rapidly without scarring. Safely sterilize anything from toothbrushes to surgical instruments. Use topically on cuts, wounds, abrasions, rashes, sunburn, insect bites, razor nicks and bandages. Spray on garbage to prevent decay odors. Mist kitchen sponges, towels, cutting boards to eliminate E. Coli and salmonella bacteria to prevent food poisoning and gastrointestinal inflammation. Add when canning, preserving or bottling. Use like peroxide on zits and acne. Add to juices and milk to prevent spoiling, fermenting, deteriorating, clabbering or curdling. Spray in shoes and between toes to stop most skin itch, athletes foot and fungi. Diminish dandruff, psoriasis, skin rashes, etc. Add to bath water, gargle, douches, colon irrigation, nasal spray and dental water-pic solutions. Cuts downtime dramatically from colds, flu, pneumonia, staph, strep, respiratory infections and rhino viruses. Skin itch, eye irritation or infection (dilute a small amount with an equal amount of distilled water for eye use) and ear infections (warm slightly first), some moles and warts vanish (put on band aid pad and wear on wart overnight each night until gone). Use with Q-tips on fingernail, toenail, and ear fungi. Can impede tooth decay and bad breath. Unlike pharmaceutical antibiotics, Colloidal Silver never permits strain-resistant pathogens to evolve. Toothaches, mouth sores, bacterial irritations are diminished. Soak dentures. Spray refrigerator, freezer and food storage bin interiors. Mix in postage stamp, envelope, and tape moistening wells, paint and paste pots to prevent bacterial growth, odors, spoiling or souring. Add to water-based paints, wallpaper paste, dishwater, cleaning and mopping solutions, etc. Spray pet bedding and let dry. Spray on top of contents of opened jam, jelly, and condiment containers and inside lids before replacing. Mix a little in pet water, birdbaths, cut flower vases. Add to swamp cooler water. Spray air conditioner filters after cleaning. Swab air ducts and vents to prevent breeding sites for germs. Use routinely in laundry final rinse water and always before packing away seasonal clothes. Damp clothes or towels and washcloths will not sour or mildew. Eliminate unwanted microorganisms in planter soils and hydroponics systems. Spray plant foliage to stop fungi, molds, rot, and most plant diseases. Silver is an excellent plant-growth stimulator. Treat pools, fountains, humidifiers, Jacuzzis, hot tubs, baths, dishwashers, re-circulating cooling tower water, gymnasium foot dips, and bath and shower mats. Spray inside shoes, watch bands and gloves and under fingernails periodically. Treat shower stalls, tubs, fonts, animal watering troughs, shavers to avoid trading germs. Rinse fruit and vegetables before storing or using. Put in cooking water. Add to human and animal shampoos and they become disinfectants. Prevent carpets, drapes and wallpaper from mildewing. Wipe telephone mouthpieces, pipe stems, headphones, hearing aids, eyeglass frames, hairbrushes, combs, loofas. Excellent for diapers and diaper rash. Do toilet seats, bowls, tile floors, sinks, urinals, door knobs. Kills persistent odors. Rinse invalid's pillowcases, sheets, towels and bedclothes. There are literally thousands of other essential uses for this odorless, nearly tasteless and colorless, totally benign, powerful, non-toxic disinfectant and healing agent. You'll find that a spray or misting bottle of Colloidal Silver solution may be the most useful health enhancement tool in your environment. Dosage: Some people, when first ingesting Colloidal Silver, have the experience that is called the Herxheimer effect. This experience is a result of the silver very efficiently killing off too many pathogens too fast for the body to dispose of through the normal eliminative organs. This forces the body to utilize the secondary eliminative organs: the lungs, sinuses and skin. Sometimes a new Colloidal Silver user who ingests Colloidal Silver for the first time will experience what feels like a cold or the flu (or diarrhea). This can be stopped by cutting back on the dosage (or completely abstaining for a day or so), or prevented by starting with a small dose (1/4 to 1 teaspoon twice a day) and gradually increasing the dosage. Some people even find they need to start off at first with just a few drops. People who are trying to self-treat a significant health condition (like asthma, or an internal infection) may need to work up to fairly large quantities each day (4 to 6 oz 2-3 times per day) before they see an improvement. There are no known instances of anyone overdosing with Colloidal Silver.