I'll try to offhand piece this together in regards to the John Oliver thing, but do so poorly and inaccurately. The DIA and CIA are in theory concerned with human intelligence (legally, foreign), the NSA is concerned with information/data/signal intelligence. Again, technically legally only "foreign" intelligence.
Traditionally the "loophole" was that they had to have only one foot in the U.S., i.e. someone here talking to someone there. The NSA realized this was a waste of time and just decided to hoover up everything in sight and figure it out later. Which is why they got caught.
The DIA and CIA have goals that are outside simple data collection and spent years on building relationships and so on, the NSA has been organized and worked in a such a way that they've never got past that data collection step so their spying is considered often almost functionally useless. You go "I need to know about this thing in placename, Russia", the CIA talks to some people and comes back "they think it's bullshit", the NSA dumps fifty gigabytes of everything a search turned up "placename, Russia" in on you.
ECHELON is rumored to have accomplished essentially nothing it was set out to do but wound up doing all sorts of other shit:
https://en.wikipedia.org/wiki/ECHELONAlso, like everything else, the FBI, CIA, DIA and NSA all have turf wars so there's endless redundancy and it's not difficult for an administration to get the intelligence it wants to see. Which is a funny lesson nobody seems to ever recall about the [insert fuckup here] the agencies played along until it was time to turn on the administration.
The advantage to the average American of the CIA is that they've been publicly burned before by the Congress, so they tended to be much more careful about staying within certain boundaries in the years after the Church Committee. The War on Terror started to reverse that. Protecting us.
The NSA not only got less than a slap on the wrist, but the DNI directly lied to Congress and kept his job. (Snowden claims this is why he whistleblew.)